help with multiple query

abbey

hello everyone i'm building this news system with a comments feature. i have two tables, one for the main news which is labeled "news" and the comments table which is labeled "news_comments".

the news table have these columns:
-id
-author
-title
-entry
-date
-time

the news comment table have these columns:
-id
-news_id (this is the id number from the news table)
-author
-title
-comment

what i really want to do is pull the amount of comments for each entry. i still cant understand multiple queries and relational tables.

so how do i do multiple select queries and match the comments to the news item that was posted?

thanks for the help everyone.

sid

table news:
-id
-author
-title
-entry
-date
-time

table comment:
-id
-news_id (this is the id number from the news table)
-author
-title
-comment

$newsId is the variable which holds the ID for the news entry you want to look up the number of comments for...

the query should look like that (at least with mySQL):

SELECT COUNT(*)
FROM news nt, comment com
WHERE nt.id = com.news_id
AND nt.id = '$newsId'

abbey

ok so here is what i have and so far i havent ran into any errors:

$comment_connection = mysql_connect($dbhost, $dbusername, $dbuserpassword);
$comment_db = mysql_select_db($default_dbname, $comment_connection);
$comment_query = "SELECT count(*) FROM $commenttable, $newstable WHERE $newstable.id = $commenttable.news_id AND $newstable.id = '$newsId";
$comment_result = mysql_query($comment_query);

no how would i go about actually showing the the number of comments per newsitem?

sorry, i'm new to this.

thanks for the help so far.

sid

$row = mysql_fetch_array($comment_result);

print "$row[0] comments";

abbey

ok here is my updated code:

$comment_connection = mysql_connect($dbhost, $dbusername, $dbuserpassword);
	$comment_db = mysql_select_db($default_dbname, $comment_connection);
	$comment_query = "SELECT count(*) FROM $newstable, $commenttable WHERE $newstable.id =\"$commenttable.news_id\"";
	$comment_result = mysql_query($comment_query);

$rowe = mysql_fetch_array($comment_result);

when i do that with the quotes in the $newstable.id=\"$commenttable.news_id\"

but when i do this:

$comment_connection = mysql_connect($dbhost, $dbusername, $dbuserpassword);
	$comment_db = mysql_select_db($default_dbname, $comment_connection);
	$comment_query = "SELECT count(*) FROM $newstable, $commenttable WHERE $newstable.id = $commenttable.news_id";
	$comment_result = mysql_query($comment_query);

$rowe = mysql_fetch_array($comment_result);

i get the number of comments in this instance 2 comments, but it says two comments for all of the newspost.

anyone have any ideas?

i'm gonna keep working on this

abbey

I GOT IT!!!!!! weeeeee