PHP/MySQl/INNER JOIN Question

[cabernet]

Here is my predicament:

Data are pulled from 2 tables
One is: "articles" the other one is: "discussion"

I would like to be able to echo the title of any articles that have been commented (In discussion table)
In order for users at first glance to be aware that comments have been added

I have for test purpose created the following little script
That uses INNER JOIN and should echo "title from articles"
Understand that I need to use join table because
I need to display "title" only if comments have been added in "discussions"
and "title column " is only found in "articles"
my test script does not create errors but results and in an empty screen!

any input?
Thanks

Regards
Henry

<?php

above are the connect usual lines....

$link_id = db_connect('fingerlakesbusinessalmanac');

$result = mysql_query(&quot;SELECT articles.ID FROM articles INNER JOIN discussions
ON articles.ID=discussions.articleID WHERE
while($query_data=mysql_fetch_array($result)&quot;,$link_id);
{
echo $query_data[&quot;title&quot;],&quot;<p>&quot;;

}

?>

 

[sleipnir214]

The code you've posted should produce a bunch of errors. Particularly your mysql_query() invocation.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[newphpbie]

$result = mysql_query("SELECT articles.ID FROM articles INNER JOIN discussions
 ON articles.ID=discussions.articleID WHERE .....

Your query is incomplete!!!

 

[sleipnir214]

newphpbie:
Didn't I just say that?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[cabernet]

OK indeed you were correct!

I changed a few things around
do not get any errors

but regardless of the chosen array
I generate pages of &quot;m&quot; what does that mean
thanks

regards

<?php

$tempdir=&quot;admin/&quot;;
require(&quot;admin/connect.php&quot;



$result = mysql_query(&quot;SELECT articles.ID FROM articles INNER JOIN discussions
ON articles.ID=discussions.articleID&quot;

while($query_data=mysql_fetch_array)
{
echo $query_data[&quot;comment&quot;],&quot;<p>&quot;;

}
?>

 

[sleipnir214]

I don't understand this line:

while($query_data=mysql_fetch_array)

It seems that you are invoking mysql_fetch_array, but function invocations in PHP require that the name of the function be followed by parentheses containing input parameters.

Also your query is only pulling out the colum ID from your table, yet you want to reference in the $query_data array an associatively-indexed element named &quot;comment&quot;. If you don't fetch the column, it won't appear in the array.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[cabernet]

I feel that I am blocking somewhere
cannot get it trough

<<<
$result = mysql_query(&quot;SELECT articles.id FROM articles WHERE title='$title' INNER JOIN discussions
ON articles.ID=discussions.articleID&quot;

while($query_data=mysql_fetch_array($result),$title)
{
echo $result[&quot;title&quot;],&quot;<p>&quot;;

}

>>>

 

[sleipnir214]

First and formost, you cannot get a column out of $result that does not appear in your SELECT query.

Your SELECT query begins &quot;SELECT articles.id[/i] FROM&quot;. This means that the only column returned by MySQL is the column named &quot;id&quot; from the table named &quot;articles&quot;. However, inside your while loop you are referring to $result['title'].

You either must use only to those columns that appear on your SELECT query or you must change your SELECT query to include the columns to which you need to use.

Second, I do not understand what

$query_data=mysql_fetch_array($result),$title

in the while clause is supposed to do. You should be getting a parse error from this line.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!