Comments Count

[GamerZ]

Hi there, I would like to know what is the best way to display a comment count?

Is it better to insert a field called comments_count in the news table and when everytime a comment is posted, the count is updated.

Or should I have 2 queries, 1 for displaying the news, 1 for counting the number of comments?

[Barq]

I think the first solution is the best but you have to update counter when comment added and when coment deleted.

[Cam]

Or a query like this (guessing your DB schema)

SELECT n.*, COUNT(c.id) AS comments_count
FROM news AS n, comments AS c
WHERE n.id = c.news_id

Then you have everything in one resultset

[GamerZ]

Or a query like this (guessing your DB schema)

SELECT n.*, COUNT(c.id) AS comments_count
FROM news AS n, comments AS c
WHERE n.id = c.news_id

Then you have everything in one resultset

thanks, that helps =D

[siteguru]

Or a query like this (guessing your DB schema)

SELECT n.*, COUNT(c.id) AS comments_count
FROM news AS n, comments AS c
WHERE n.id = c.news_id

Then you have everything in one resultset

Do you need the AS assignment for the tablenames? I have used those types of SQL commands many times but I have never used AS when defining the alias for the tablename, I would typically use ...

FROM news n, comments c

To go one step further, I would also typically use JOINs on the tables rather than the method shown ... both work fine on mySQL but JOINs use a more standard SQL syntax and so are (in theory) portable between database types. (Just my 2¢).

[GamerZ]

Do you need the AS assignment for the tablenames? I have used those types of SQL commands many times but I have never used AS when defining the alias for the tablename, I would typically use ...

FROM news n, comments c

To go one step further, I would also typically use JOINs on the tables rather than the method shown ... both work fine on mySQL but JOINs use a more standard SQL syntax and so are (in theory) portable between database types. (Just my 2¢).

care to give an example? I couldt think of a JOIN statement here.

[GamerZ]

Or a query like this (guessing your DB schema)

SELECT n.*, COUNT(c.id) AS comments_count
FROM news AS n, comments AS c
WHERE n.id = c.news_id

Then you have everything in one resultset

Got an error, need to have GROUP BY. I am groupign it by post_id. Am i correct?

And when there is no comment, it will not display 0.

[GamerZ]

Up

Anyone pls?

[Cam]

I'd imagine so, yes.

Siteguru: You don't need AS, I just like to use it. Personal preference I guess.

[Chris82]

Since the query uses an aggregate function (count) you need to use group by.

SELECT n.*, COUNT(c.id) AS comments_count FROM news AS n, comments AS c WHERE n.id = c.news_id;

The aliases for tables are mostly used to save some typing when tables have rather long names and identifally named columns.

[GamerZ]

This is my full SQL code queried on myadmin. It works, but it does not return 0 if there is no comments for tt post. Any idea how to solve it?

SELECT gamerz_posts. * , COUNT( gamerz_comments.comment_id )  AS comments_count FROM gamerz_posts, gamerz_comments WHERE gamerz_posts.post_id = gamerz_comments.comment_postid GROUP  BY gamerz_comments.comment_id

[siteguru]

SELECT gp.*, COUNT(gc.comment_id) AS comments_count 
FROM gamerz_posts gp 
INNER JOIN gamerz_comments gc ON gp.post_id = gc.comment_postid 
GROUP BY gc.comment_id

That's how I would have written that SQL command. Bear in mind that for any field represented by gp.* you will only see one result per row because of the GROUP BY clause. To be even more pedantic, you would be better off to actually define the fields you want - it might mean more typing now but it will make it easier to understand what's going on if you ever need to come back to this code.

PS - note I have no space between COUNT and ( ... in some database types (like mySQL) this can be important.

[GamerZ]

thanks alot, but still will not rtrun 0, guess that i have to add a field =(