Posts and Comments Counting...

bladx

Hey everyone,

I have two tables: Posts and Comments.

What I am doing right now is displaying posts on a html page. Below each post, I would like to put the total number of comments each particular post has.

How can I do this in one query instead of multiple queries (for each post...)?

Thank you for any help,

Andy

alimadzi

Not having your database schema right in front of me to refer to, I'll just use a generic query...

SELECT p.post_id, p.post_title, COUNT(c.comment_id)
  FROM Post p
  LEFT JOIN Comment c ON p.post_id = c.post_id
  GROUP BY p.post_id
  ORDER BY p.pub_date DESC

That should give you the post id and title along with a comment count for each one. Obviously you'll need to adjust the table/column names to your own DB, but you probably get the idea.

bladx

Thanks for the quick reply!

Yeah, that's exactly what I tried earlier. But for some reason, it didn't work as expected. I kept getting an error next to the GROUP BY part.

Here's what my query looks like:

SELECT SQL_CALC_FOUND_ROWS wb_entries.*, wb_categories.cat_name,
wb_groups.*, count(wb_comments.id),
UNIX_TIMESTAMP(e_date_created) as e_date_created_timestamp
FROM wb_entries
LEFT JOIN wb_categories ON wb_entries.e_category = wb_categories.id
LEFT JOIN wb_groups ON wb_entries.e_group = wb_groups.id
LEFT JOIN wb_comments ON wb_comments.c_comment_on_entry = wb_entries.id
WHERE e_author = 2 AND e_privacy_level = 1
ORDER BY e_date_created
GROUP BY wb_comments.c_comment_on_entry
DESC LIMIT 0, 10

And here is the error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY wb_comments.c_comment_on_entry DESC LIMIT 0, 10' at line 1

Any idea?

bladx

Oh, I didn't know you have to order it: GROUP BY (and then) ORDER BY...
If you use ORDER BY (and then) GROUP BY I get the error.

But I'm still not quite at the point I am trying to get at... with the query I'm using now, I just get entries that have at least one comment or more... and it's not selecting entries that don't have any comments. So, I guess I'll try playing around with it a bit more.

alimadzi

You're only getting entries with comments because you're grouping by 'c_comments_on_entry'. You should be grouping by 'wb_entries.id'.

bladx

ah haa...

Okay, sorry for the easy question... I just didn't see that answer yesterday. Thanks!

rulian

I've had someone suggest, and i think it is a good idea, that rather then transversing tables and counting rows, to have a sepeate table for a each title and simply keeping a view/comments/whatever column in it updated, that way when you query your selecting a table row rather then transversing tables