php and mysql Count(*)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
zactanaz
Forum Newbie
Posts: 4
Joined: Sun Jan 16, 2005 2:53 pm
Location: California,USA

php and mysql Count(*)

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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";
Post Reply