Have Geek Brain

Splitting a MySQL SELECT statement based on data in a column

I need to get data (vehicle data, in this case) from a MySQL table and then view the results to create separate lists for each vehicle category. Is there an easy way to do this without having to have a SELECT statement for each vehicle type?

If I was only 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. This needs to be done for four different vehicle types.

Thanks!

+2


a source to share


2 answers


Based on the Mark Answer answer, you can select all vehicles and change 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 ?>
+4


a source


You can use this:

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


This will return all four types of cars nicely grouped together for easy iteration. If you want all types of vehicles, you don't need a WHERE clause.

+2


a source






More articles:


All Articles