Counting in joined MySQL tables

[otem]

I have three tables that I have joined together article, tags and comments.

Using the following command I can ouput data from all three tables for the five most recently published articles:

$result=mysql_query("SELECT * FROM article, tags, comments WHERE article.published=tags.published AND article.published=comments.published AND article.url=tags.url AND article.url=comments.url ORDER BY article.published DESC LIMIT 5");

However, I'm also seeking to COUNT the number of "comment" in the comments table that match, so I added the COUNT(comment) line:

$result=mysql_query("SELECT *, COUNT(comment) FROM article, tags, comments WHERE article.published=tags.published AND article.published=comments.published AND article.url=tags.url AND article.url=comments.url ORDER BY article.published DESC LIMIT 5");

That didn't work, so I also tried this variation:

$result=mysql_query("SELECT *, COUNT(comments.comment) FROM article, tags, comments WHERE article.published=tags.published AND article.published=comments.published AND article.url=tags.url AND article.url=comments.url ORDER BY article.published DESC LIMIT 5");

That didn't seem to work either.

After typing this up I'm thinking that maybe its because only one match is being made at a time, but shouldn't the COUNT still work, even if it only outputs 1? Right now I'm getting:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /html/index.php on line X.

Might somebody shine a light on why its not even outputting comment count, even if it was one? Are counts not possible when tables are linked?

Thanks.

[FalseDawn]

COUNT() is an aggregate function and if combined with non-aggregated columns (the SELECT * bit), you will need a GROUP BY clause.

I'm not sure exactly what it is you are trying to count, but your best best is probably to use a separate query for it (or a subquery if your version of MySQL supports it)

Or ditch the SELECT *, explicitly list the column names you need, and add them to a GROUP BY clause.