One query; 2 tables

cannot perform the query

[henry0]

Table comments contains art_ID
Table content contains ID

Both art_ID and ID do have the same value and ref to the same piece of info
Actually an ID but do not refer to it by using the same column name!

Unfortunately I did not setup the tables and did not start writing the code that is now too close of being achieved; I cannot in that state modify it in depth

Here is the query I need to perform

Query art_ID from comments
verify that art_ID is populated

then query content table
by passing art_id value to ID and echo some values

reasons:
table comment is populated if comments are added
and use an ID that ref to an existing content with its own ID (same in both case but diferent column name
I try to find a way to signal in content that comment have been added.

<<<
mysql_query("SELECT art_ID FROM comment");
$ID=art_ID;
if
($ID >0) {
$result=mysql_query("SELECT ID FROM content where id=$ID");
while($query_data=mysql_fetch_array($result) ) {
echo $query_data ["title"],"<p>";
}
}
>>>

I cannot find a way to make it working

Thanks
Regards
Henry

[DrDoc]

What you want to do is join the tables:

$result = mysql_query("SELECT content.ID FROM content INNER JOIN comment ON content.ID=comment.art_ID WHERE while($query_data=mysql_fetch_array($result) ) {
echo $query_data["title"],"<p>";
}

[DrDoc]

MySQL's Web site might have some nifty info for you:
[mysql.com...]

[henry0]

DrDoc,
Thanks for the consultaion

regards

Henry

[lorax]

I think I understand what you're looking for.

[content]
ID ¦ a field for text

[comments]
art_ID ¦ a field for text

And you want to retrieve the text from [comments] if a comment exists for the corresponding ID in [content]?

SELECT *
FROM content
Left Join comments ON content.ID = comments.art_ID
WHERE content.ID = '$ID'

This will pull the data from both tables where the two IDs match. All you need to do is when you output the fields in the array is test for NULL or whatever your default value is if there are no comments.

Perhaps someone might have a more elegant way?

<added>sorry - looks like I was a bit late to respond!</added>

[coopster]

Actually, that step can be done in the query via LEFT JOIN and testing for NOT NULL, assuming that comment.art_ID is declared NOT NULL:

$sql = 
"SELECT content.ID FROM content  
LEFT JOIN comment  
ON content.ID = comment.art_ID  
WHERE comment.art_ID IS NOT NULL"; 

Regards -- coopster

[henry0]

Domino effect, this is what knowledge is about
With acquired knowledge one has a need and will always find usage for more complicated output that in turn will soon (hopefully!) become obvious etc.
Thank you to WebmasterWorld and you all

[lorax]

>> and testing for NOT NULL

I'm glad there are folks like you around to correct me! ;)

[coopster]

I didn't correct you, lorax, merely offered another way of accomplishing the task ;)