Cut down on mysql count commands

[mikwit]

So I'm having some trouble with a concept, I was wondering if you guys know how to do this efficiently.

I'm running a site that is basically like a forum. I have 3 mysql tables, one for posts, one for comments, and one for a list of boards.  In the posts there is a col. for which board the post belongs to.  I then print out all of those onto the boards page, but I'd also like to know how many comments there are for each post, but I can't figure out a way to match the counts of the comments up with the posts without doing an individual query for each post

$sql = 'SELECT COUNT(*) AS CNT FROM comments WHERE replyto='.$number; 

I was wondering, how do forums do this, is there a way to skim through the posts, find all their numbers, then get the count for each of them in one larger query?  Are the count querys so quick it doesn't matter?

Any advice would be helpful

[Psycho]

Need to see the layout of your tables to really provide the proper query. But, your query would look something like this:

 

SELECT posts.*, COUNT(comments.replyto) as comment_count
FROM posts
LEFT JOIN comments ON posts.id = comments.replyto
GROUP BY replyto

 

This would pull a list of all posts records and their data along with another field value for the comment count.

[mikwit]

That did the trick, thanks a ton, never knew mysql that well hopefully this will lead to a better understanding (any maybe a few hours of skimming the manual wouldn't hurt).

[mikwit]

I'm having a little trouble on where to place the WHERE command, I probably should have sayed this in the original post but the query is like

SELECT * FROM `posts` WHERE school='.$school_id.' LIMIT 0 , 15

but I don't know where in your code to place the WHERE to say which posts belong to that board... I tried placing it in a variety of places but it keeps giving me errors. Any thoughts?

[Psycho]

$query = "SELECT p.*, COUNT(c.replyto) as comment_count
          FROM posts p
          LEFT JOIN comments c ON p.id = c.replyto
          WHERE p.school={$school_id}
          GROUP BY c.replyto
          LIMIT 0 , 15";

 

http://dev.mysql.com/doc/refman/5.0/en/select.html

[mikwit]

Okay, so this works when there was one entry that matches the where clause, but when there are two it only returns the first one in the table.  It's not limit related, I tried reading the reference but I don't even know where to start with trouble shooting this.  Thanks a lot, I know you guys don't have to be doing this and I really appreciate it.

[fenway]

Can you give an example of what doesn't work?