Page 1 of 1

php and mysql Count(*)

Posted: Sun Jan 23, 2005 10:40 pm
by zactanaz
Ok I have this, lets say i have a sql table about articles that users send... i want a code to count the articles that each user send and return the username and the number of articles each user has send...

the result would be something like:

Users # Articles

1. Username1 - 23
2. Username2 - 21
3. Username3 - 17
4. Username4 - 10

i know has to do something with the COUNT(*) function of mysql but i had no luck

or maybe i am worng...

thanks

Posted: Sun Jan 23, 2005 11:08 pm
by feyd

Code: Select all

SELECT a.`Username`, COUNT( a.`Username` ) `num` FROM `table` a, `table2` b WHERE b.`author` = a.`Username` GROUP BY b.`author`
I think.

Posted: Sun Jan 23, 2005 11:26 pm
by timvw
extended that a little, because i always wanted to show messages without linked comments ;)

Code: Select all

SELECT 
 m.message_id AS message_id, 
 m.title AS title, 
 m.content AS content, 
 m.datetime AS datetime, 
 IF(ISNULL(comment_id), 0, COUNT(*)) AS count 
FROM message AS m 
LEFT JOIN comment AS c USING (message_id) 
GROUP BY m.message_id 
ORDER BY m.datetime";