View Full Version : Displaying number of comments next to news article
03-23-2004, 09:14 PM
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:
SELECT n.News, a.aId
FROM news as n
RIGHT JOIN articles as a
ON (a.commentForId = n.newsId)
ORDER BY n.newsId DESC
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?
03-23-2004, 09:31 PM
MySQL has a count() (http://www.mysql.com/count) function :)
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.
03-23-2004, 09:36 PM
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?
03-23-2004, 10:12 PM
Im looking at the docs and dont have a clue how I could form a query to do this, I thought something like this:
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.
03-23-2004, 10:15 PM
Not sure, might be something like COUNT(s.*)
03-23-2004, 10:16 PM
ok, ive really got to get a test set up and have a go, thanks so far :)
03-24-2004, 02:12 PM
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 would be the numder of rows where parentid =0
03-24-2004, 05:21 PM
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.
03-24-2004, 05:25 PM
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
03-24-2004, 06:04 PM
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.