[Resolved] mySQL JOIN tables

NateDawg

I have a mySQL table that contains news and one that contains comments. What I'm trying to do is get the number of comments for each news post. This is the code I'm using now...

//For each news post get the number of comments
while ($newarray = mysql_fetch_array($news_query)){
//news arrays go here	

$num_results = mysql_query("SELECT COUNT(*)
FROM comments where id='$id'");
$num_results = mysql_fetch_array($num_results);
}
}

But this makes a new query for each news post, and if I have 100 news posts then I will have 100 queries. Thats alot of work for one page load so I need a way to make this simple. I was thinking I might need to JOIN the tables? Any ideas?

Drakla

You could do something like this

SELECT id, count(*) FROM comments GROUP BY id

Then when you retrieve each row the first field will be the article id, the second the number of comments it has. You could even sort by popularity by doing

SELECT id, count(*) as numcomments FROM comments GROUP BY id ORDER BY numcomments DESC

I'm assuming that the id of the comments table is the id of the article it's the comment for.

NateDawg

OK here is my query for the news

$news_query = mysql_query("SELECT * FROM news order by id ".$config['news_order']." LIMIT $from, ".$config['news_max_results']."")

Do I...or how can I add
SELECT id, count(*) FROM comments GROUP BY id
to this?

Drakla

If you're pulling up information on the articles and printing that out, do a separate query for each item. Put in whatever fields you've got rather than this trivial stuff.

$q = "SELECT * FROM news order by id " . $config['news_order'] . " LIMIT $from, " . $config['news_max_results'];

$news_query = mysql_query($q);

while ($row = mysql_fetch_assoc($news_query) )
{
	$q = "SELECT count(*) FROM comments WHERE id = $row[id]";
	$res = mysql_query($q);

$numComs = mysql_fetch_row($res);

echo 'For article ', $row['id'], ' there are ', $numComs[0], ' comments<br />';
}

NateDawg

Yeah thats how I originally had it. I just wanted to know if there was a way to do it with just one query?

Drakla

Sorry dude, span out there... Well you probably could do this,

SELECT news., count() as numcomments FROM news, comments WHERE news.id = comments.id GROUP BY news.id [plus all your required sorting]

If you've got an index on the comments id column that would really help, otherwise it's a bit of an 'ouch' query. I'm pretty sure that'll work but my mysql server keeps crashing when I try to create some temp tables to test it.

I'll reboot and make sure, concientious I am.

EDIT - oops, forgot the group by, realised the second I pressed restart.

NateDawg

I got it working now. Thanks you very much Drakla!

Drakla

Whah! Five minutes after getting into bed a spectre came in and niggled me about my solution - it'll only bring back items that have at least one comment. If you want all things returned this is left join territory.

So for an all item solution use

SELECT news.*, count(commends.id) as numcomments FROM news LEFT JOIN comments ON news.id = comments.id GROUP BY news.id [plus all your required sorting]

And I think that should do nicely.