DISTINCT with COUNT in <select> field

[jonki]

Hi everyone,
I am really close to figuring out this and I just need to piece it together.
I am creating a data base which I want people to be able to search only for what is actually in there so I want to create a pull down that contains what is in the data base. I have done this and it works great but I would like to go one step further and list the count() next to the item in the select object (pull down) in the form.

This is what I have so far;

$i=0;
				$result = mysql_query("select distinct type from accesories where 1 order by type") 			
				or die (mysql_error() );

						while ($row = mysql_fetch_assoc ($result))
						{
						$a = $row["type"]; 
						$type[$i]=$a;
						$i=$i+1;
						}			  
		  ?>
		  <select name="type" size="1" id="type">
		 <? $j=0;
		 	while ($j < $i)
						{ ?>
						 <option><? echo $type[$j]; ?></option>
						<?
						$j++;
						} 

I need to combine the distinct (or group by) with count() but I dont quite know how to separate them off to be able to use them in the form.

What I want is this

 <select name="type" size="1">
                <option>Alloys (14)</option>
                <option>Back lights (47)</option>
                <option>Radio CD (2)</option>
                <option>Spoiler (4)</option>
              </select>

etc .... I just dont know how

Many thanks once again

[NogDog]

I think what you want is:

$query = "SELECT type, COUNT(*) AS ct FROM accessories GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
echo "<select name='type' size='1'>\n";
while($row = mysql_fetch_assoc($result))
{
   printf("<option value='%s'>%s (%s)</option>\n", $row['type'], $row['type'], $row['ct']);
}
echo "</select>\n";

[jonki]

That looks perfect. Exaclty what I need.

I will try it first thing tomorrow.

Many many thanks