Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Senior Coder missing-score's Avatar
    Join Date
    Jan 2003
    Location
    UK
    Posts
    2,194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Displaying number of comments next to news article

    I hope the title described well enough what I want to do. I want to have some loaded articles from a database, and then at the bottom I want a link that says:

    (x) Replies - Read Replies.

    There would be the replies stored in another table. I thought something like this:

    Code:
    SELECT n.News, a.aId
    FROM news as n
    RIGHT JOIN articles as a
    ON (a.commentForId = n.newsId)
    ORDER BY n.newsId DESC
    LIMIT 5
    However that woud return Id's of replies, and I would rather just know how many replies there are... Can this be done a better way, or will I have to select at least some data to be able to count it.

    By the way, I would like to keep this to as few queries as possible, I know it could be done in 2, so Im looking for a one query method, or an efficient 2 query method... does anyone have any ideas?

  • #2
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,265
    Thanks
    6
    Thanked 48 Times in 48 Posts
    MySQL has a count() function

  • #3
    Az`
    Az` is offline
    New Coder
    Join Date
    Mar 2004
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You could assign the above query to a string, then just do:

    $num_replies = mysql_num_rows($query_result).

    Then just put $num_replies as the number of replies.
    Last edited by Az`; 03-23-2004 at 10:34 PM.

  • #4
    Senior Coder missing-score's Avatar
    Join Date
    Jan 2003
    Location
    UK
    Posts
    2,194
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the reply Az`, however it is more complex than that, as I want a separate number of replies for multiple articles.

    Nightfire, I realised there was a count function however when I have used it before it wouldnt let me select any other data, on its own it would, so I assumed that wouldnt work...

    Would the count function still be useful, or would it return a count of ALL the rows returned?

  • #5
    Senior Coder missing-score's Avatar
    Join Date
    Jan 2003
    Location
    UK
    Posts
    2,194
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Im looking at the docs and dont have a clue how I could form a query to do this, I thought something like this:

    Code:
    SELECT n.*, s.COUNT(*)
    FROM news as n, subnews as s
    WHERE n.Id == s.commentForId
    would this work? also, I see the GROUP BY alot with the count function, how does this work. I searched google and mysql.com, but it just came up again with the GROUP BY functions and I couldnt find anything on how to use group by.

    Thanks.

  • #6
    Senior Coder Nightfire's Avatar
    Join Date
    Jun 2002
    Posts
    4,265
    Thanks
    6
    Thanked 48 Times in 48 Posts
    Not sure, might be something like COUNT(s.*)

  • #7
    Senior Coder missing-score's Avatar
    Join Date
    Jan 2003
    Location
    UK
    Posts
    2,194
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok, ive really got to get a test set up and have a go, thanks so far

  • #8
    Regular Coder
    Join Date
    Mar 2004
    Posts
    115
    Thanks
    0
    Thanked 0 Times in 0 Posts
    surly if you comments are in seperate table with a parent news id

    all you would have to do is

    $e=mysql_query("SELECT parentid as count FROM comments WHERE parentid='0'");
    $f=mysql_num_rows($e);


    $f would be the numder of rows where parentid =0

  • #9
    Senior Coder missing-score's Avatar
    Join Date
    Jan 2003
    Location
    UK
    Posts
    2,194
    Thanks
    0
    Thanked 0 Times in 0 Posts
    no, it wouldnt work, becuase just say i wanted to do this:

    SELECT parentid as count FROM comments WHERE parentid='0' or parentid = '1' or parentid = '2'

    That would count all the comments for 3 articles, when i want an idividual count for each one.

  • #10
    Regular Coder
    Join Date
    Mar 2004
    Posts
    115
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yes but i thought you would be cycling through the query to dispay the articles and then doing a query within loop to find count on comments for each article

  • #11
    Senior Coder missing-score's Avatar
    Join Date
    Jan 2003
    Location
    UK
    Posts
    2,194
    Thanks
    0
    Thanked 0 Times in 0 Posts
    no, becuase if i wanted 10 articles on a page, I would have to do 11 queries to the database.. I want all this in one query, and I know it can be done.


  •  

    Posting Permissions

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