homepage Welcome to WebmasterWorld Guest from 54.225.57.156
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Counting in joined MySQL tables
otem




msg:3309634
 6:19 pm on Apr 12, 2007 (gmt 0)

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




msg:3309902
 12:42 am on Apr 13, 2007 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved