Separating MySQL SELECT statement based on data in a column

Question

Hi everyone,

I need to retrieve data (vehicle details, in this case) from a MySQL table tabled and then loop through the results to generate separate lists for each category of vehicle. Is there a simple way to do this without having to have a SELECT statement for each type of vehicle?

If I were just doing this for one category, I would use the following:

<?php
$sql = "SELECT * FROM apparatus WHERE vehicleType = 'Support';
$getSQL = mysql_query($sql);
?>

<ul>
<?php while ($vehicleData = mysql_fetch_assoc($getSQL)) {?>
<li><?php echo $vehicleData['name'];?></li>
<?php } ?>
</ul>

..etc. Need to do this for four different types of vehicles.

Thanks!

Answer 1

You could use this:

SELECT * FROM apparatus
WHERE vehicleType IN ('foo', 'bar', 'baz', 'qux')
ORDER BY vehicleType

This will return all four vehicle types, nicely grouped for easy iteration. If you want all the vehicle types, you don't need the WHERE clause.

Answer 2

Building on Mark's Answer, you can select all the vehicles, and rearrange your result set in php:

<?php
  $sql = "SELECT * FROM apparatus ORDER BY vehicleType";
  $getSQL = mysql_query($sql);
  // transform the result set:
  $data = array();
  while ($row = mysql_fetch_assoc($getSQL)) {
    $data[$row['vehicleType']][] = $row;    
  }
?>
<?php foreach ($data as $type => $rows): ?>
  <h2><?php echo $type?></h2>
  <ul>
  <?php foreach ($rows as $vehicleData):?>
    <li><?php echo $vehicleData['name'];?></li>
  <?php endforeach ?>
  </ul>
<?php endforeach ?>