List and count values in a table

[simonp]

Hi,

 

I thought what I was trying to do was simple but appears to be a little beyond my mysql knowledge

 

I have a table (populated by a drop down box on a web page) which looks a bit like:

 

Dave

Will

Simon

Andy

Will

Simon

Dave

Dave

Chris

Dave

 

... and I need to be able to query the number of times each name appears (extra names may be added in the future to can't hard code the names).

 

So I should end up with

 

Dave - 4

Will - 2

Simon - 2

Andy - 1

 

So far I have:

 

$query = "SELECT * FROM table WHERE `fieldid` = 1 ORDER BY value ASC";
$result=mysql_query($query);
while($data = mysql_fetch_array($result)) {
$value = $data["value"];
$uses = $data["uses"];
$reportdata["tablevalues"][] = array($value,$uses);
}

 

...which just lists all the name.

 

I think I need a foreach and maybe a count (*) but am not having much joy - any help appeciated!

 

 

Simon

[sader]

SELECT *,COUNT(name) AS cnt FROM table WHERE fieldid=1 GROUP BY name ORDER BY value ASC

 

and then use $row['cnt']