SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: Comments Count

  1. #1
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Comments Count

    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?

  2. #2
    SitePoint Zealot
    Join Date
    Dec 2002
    Posts
    106
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think the first solution is the best but you have to update counter when comment added and when coment deleted.

  3. #3
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    Or a query like this (guessing your DB schema)
    Code:
    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

  4. #4
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DJ P@CkMaN
    Or a query like this (guessing your DB schema)
    Code:
    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

  5. #5
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DJ P@CkMaN
    Or a query like this (guessing your DB schema)
    Code:
    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 ...
    Code:
    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˘).
    Ian Anderson
    www.siteguru.co.uk

  6. #6
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by siteguru
    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 ...
    Code:
    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.

  7. #7
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DJ P@CkMaN
    Or a query like this (guessing your DB schema)
    Code:
    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.

  8. #8
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Up

    Anyone pls?

  9. #9
    ********* Wizard silver trophy Cam's Avatar
    Join Date
    Aug 2002
    Location
    Burpengary, Australia
    Posts
    4,495
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)
    I'd imagine so, yes.

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

  10. #10
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since the query uses an aggregate function (count) you need to use group by.

    Code:
    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.

  11. #11
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Code:
    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

  12. #12
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,631
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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.
    Ian Anderson
    www.siteguru.co.uk

  13. #13
    SitePoint Guru GamerZ's Avatar
    Join Date
    Nov 2001
    Location
    Singapore
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks alot, but still will not rtrun 0, guess that i have to add a field =(


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •