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)

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 :)

03-23-2004, 09:31 PM
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.