MySQL: printing data just once for each grouping

Question

I'm coding in PHP/MySQL and have the following query to fetch products and product group data:

SELECT products.id,products.name,product_groups.id,product_groups.name
FROM products
INNER JOIN product_groups
ON products.id=product_groups.id
WHERE products.name LIKE '%foobar%'
ORDER by product_groups.id ASC

So this query fetches products and orders them by product group. What I would like to have is to display product_groups.name just once for each product grouping. So even if I have ten shoe products, the group name "Shoes" is only displayed once.

I'm using the following PHP to print out the results:

while ($data = mysql_fetch_array($result))

Answer 1

while($data = mysql_fetch_assoc($result)){
    if($data['product_groups.name'] != $groupname){
        echo "Groupname: ".$data['product_groups.name']."<br />";
        $groupname = $data['product_groups.name'];
    }
    echo "ID: ".$data['products.id']."<br />";
    echo "Name: ".$data['products.name']."<br />";
}

Maybe you can do like this!?

Answer 2

If you want it done in the MySQL query, it is honestly more trouble than it's worth. For one, the syntax is really wonky (as I recall) to have a group name listed at the top of each grouping. And the results are still treated as rows, so the group name will be treated like a row with all the other columns as Null, so you won't really save any time or effort in the PHP script as it has to do an if statement to catch when it hits a group name instead of the group data.

If you want it done by the PHP while loop, Johan is on the right track. I use the following for a similar situation:

$result = $sql->query($query);

$prev_group = "";

while($data = $result->fetch_assoc()){
   $curr_group = $data['group'];
   if ($curr_group !== $prev_group) {
      echo "<h1>$curr_group</h1>";
      $prev_group = $curr_group;
    }
    else {
        echo $data;
        .....
     }

Obviously the echo data would be set up to echo the parts of the data the way you want. But the $prev_group/$curr_group is set up so that the only time they won't match is when you are on a new group and thus want to print a header of some sort.