Display list of cars from MySQL

[Unknown98]

I have a table, with about a hundred cars in it. There's three columns, id, car name, and one for manufacturer. What I'd like to do, is display a table listing all manufacturers and how many cars each has assigned to it. For example, say I have 5 Dodge cars in the table and 7 Fords. I'd like it to look like this:

 

Dodge | 5

Ford | 7

 

etc... for all manufacturers. I think I would have to use some form of COUNT() in my query correct? And would I need a for or while loop?

[teynon]

Do it all in your MySQL statement.

SELECT Make, COUNT(*) FROM `cars` GROUP BY Make

[Unknown98]

So, something like this?

 

$query = sprintf("SELECT car_manufacturer, COUNT(*) FROM `car_pool` GROUP BY car_manufacturer");

 

How would I go about displaying it on the page?

[teynon]

$sql="SELECT car_manufacturer, COUNT(*) FROM `car_pool` GROUP BY car_manufacturer";

if ($query=@mysql_query($sql)) {
   if (@mysql_num_rows($query)) {
      while ($req=@mysql_fetch_array($query)) {
         echo $req[0] ."|".$req[1];
      }
   }
}
else {
   die(mysql_error());
}

[jcbones]

You don't need sprintf() on that string.

 

$sql = 'SELECT car_manufacturer, COUNT(*) as car_count FROM car_pool GROUP BY car_manufacturer';
$result = mysql_query($sql) or trigger_error($sql . ' has an error<br />' . mysql_error());
if(mysql_num_rows($result) > 0) {
  while($row = mysql_fetch_assoc($result)) {
     echo $row['car_manufacturer'] . ' | ' . $row['car_count'] . '<br />';
  }
}
else {
    echo 'There is NO cars selected!';
}

 

Teynon has a typo, this should work.

[teynon]

Where's the typo? I'm not seeing it.

[jcbones]

Calling the same function twice, which doesn't get the data.

if (@mysql_num_rows($query)) {
      while ($req=@mysql_num_rows($query)) {

[teynon]

Ah, didn't notice that. Heh. Fixed.

[Unknown98]

Ah! It's working perfectly  Thanks teynon & jcbones.