Counting each username in a table

Question

Possible Duplicate:
MYSQL COUNT GROUP BY question

enter image description here

$query = "SELECT * FROM product_id_1";

$result = mysql_query($query);

while( $record = mysql_fetch_array($result) ){

$array[] = $record['username'];

$unique_array = array_unique($array);

}


foreach( $unique_array as $list ){
    echo $list.'<br/>';
}

Greeting,

I'm stuck trying to figure out how to apply array_count_unique(). Reason is I need to find out the count of each individual user in the table, i.e how many times adam appears and how many times abcd appears.

UPDATE

This is what I've done according to you guys's suggestion:

$query = 'SELECT username, COUNT(*) AS username_count FROM product_id_1 GROUP BY username';

$result = mysql_query($query);

while ($record = mysql_fetch_object($result)) {

    echo $record->username;

    echo $record->username_count;
}

Please, don't use `mysql_*` functions for new code. They are no longer maintained and the community has begun the [deprecation process](http://goo.gl/KJveJ). See the [**red box**](http://goo.gl/GPmFd)? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide, [this article](http://goo.gl/3gqF9) will help to choose. If you care to learn, [here is a good PDO tutorial](http://goo.gl/vFWnC). — PeeHaa, Sep 26 '12 at 17:59
Highly wasteful code. you don't need to 'unique' your array on every iteration of the loop. A single unique call after you've fetched your db would be more efficient. And even more efficient would be to do the counting in the database itself, so you're not fetching and throwing away all kinds of data you don't need. — Marc B, Sep 26 '12 at 18:00
@PeeHaa Not the same, I'll look into the mysql thingy, but not today. — Jian Short, Sep 26 '12 at 18:55

score 3 · Accepted Answer · answered Sep 26 '12 at 18:06

3

This is exactly why we have RDBMS and SQL:

SELECT COUNT(1) AS username_count, username
FROM
    product_id_1
GROUP BY
    username

answered Sep 26 '12 at 18:06

Sean Bright

118,630
17
138
146

This is what I've done as suggested: – Jian Short Sep 26 '12 at 18:51
I've changed my codes, thanks. Question, why pass in 1 into the COUNT()? – Jian Short Sep 26 '12 at 18:57
Convention. You can use `COUNT(*)` if it makes you feel better - see http://stackoverflow.com/questions/1221559/count-vs-count1 – Sean Bright Sep 26 '12 at 18:58

Counting each username in a table

1 Answers1