Counting how many...mysql

crazy_fool

Well i am trying to setup a stats page for a site with atleast 600 members, and i want to know how i can use mysql to count how many users have created feedback and how many times.
This is my database table
id | feedbktitle | feedback ..... (And so on)
1 | Nice | Blah blah blah ....
2 | WOW | Blah blah blah ....
1 | Great | Blah blah blah ....
and so on
Well ovbiously i can use the mysql_num_rows and it will tell me how many members have sent feedback.
But i want to know how many times each id has
So example is i know that id 1 has sent 2 feedbacks, and id 2 has sent 1. that is simple to work out, but if all members post 2 feedback each then i wont have to time to count each, so please can guide me in a way that will help me solve this problem. Thanks

Joseph07

SELECT COUNT(id) as countid FROM table WHERE id = 1

crazy_fool

So that would count all of the id's?
oh and how would i output this to the user?

Joseph07

The above will count how many times this id had appear in the database which is ID 1.

Simple way which is,

$Q = mysql_query( "SELECT COUNT(id) as countid FROM table WHERE id = 1" );
$R = mysql_fetch_assoc( $Q );
echo "Id 1 has $R['countid'] feedback"

crazy_fool

Oh thanks, so i have to do this for each 600 of my members id's?
Isn't there a way for it to do it for all 600 members?

dougal85

You can do it with GROUP BY and COUNT

crazy_fool

oh, well please can you give me an example, because am not very good with mysql. Thanks

bpat1434

SELECT COUNT(*) AS count, id
FROM tablename
GROUP BY id

And if you wanted the total number of feedbacks without using mysql_num_rows you could add a "WITH ROLLUP" which will give you the sum of all the count(s) (or how many feedbacks are in the table 🙂 )

SELECT COUNT(*) AS count, id
FROM table
GROUP BY id
WITH ROLLUP

EDIT: Woops... forgot the id field in the SELECT clause 🙂
EDIT 2: To output to the user, you'd do something like:

$query = "SELECT COUNT(*) AS `count`, `id`
          FROM `tablename`
          GROUP BY `id`";
$result = mysql_query($query) or die('MySQL Error (#' . mysql_errno() . '): ' . mysql_error());
while($row = mysql_fetch_assoc($result))
{
    echo 'Item #' . $row['id'] . ' has received ' . $row['count'] . ' feedbacks.<br />';
}

EDIT 3: Forgot to alias the COUNT() function so we could refer to it by name.... it's too early for thinking 🙂

crazy_fool

Well. thank you for all this information its great. and its doing well for me 😃