'How to group a column and skip repeating elements

I want to group a category column from my table which have repeating rows for related items.With the following code i tried, Its repeating last category for all items.

I tried this code:

$category = '';
while($row = $query->fetch(PDO:: FETCH_ASSOC)){
 $allitems[] = $row;
 if($row['category'] != $category){
     $cat = '<tr>
                <td>'.$row['category'].'</td>
             </tr>';
     $category = $row['category'];
 }      
} 
foreach($allitems as $item){
echo $cat;
echo '<tr>
        <td>'.$item['item'].'</td>
      </tr>';
}

Sample data from table:

$sample_data = [{"category":"Fruits","item":"Apple"},{"category":"Fruits","item":"Banana"},
                {"category":"Fruits","item":"Orange"},{"category":"Vegetables","item":"Tomato"},
                {"category":"Vegetables","item":"Onion"},{"category":"Vegetables","item":"Pumpkin"}]

Desired output:

enter image description here

php


Solution 1:[1]

You just need to compare the category of the previous item, to that of the current one - and if they differ, you output the category first. (Your data needs to be properly sorted by category to begin with.)

$sample_data = json_decode('[{"category":"Fruits","item":"Apple"},{"category":"Fruits","item":"Banana"},
                {"category":"Fruits","item":"Orange"},{"category":"Vegetables","item":"Tomato"},
                {"category":"Vegetables","item":"Onion"},{"category":"Vegetables","item":"Pumpkin"}]', true);
                
echo '<table>';
$previous_category = null;
foreach($sample_data as $item) {
  if($previous_category !== $item['category']) {
    echo '<tr><td><b>' . $item['category'] . '</b></td></tr>';
  }
  echo '<tr><td>' . $item['item'] . '</td></tr>';
  $previous_category = $item['category'];
}
echo '</table>';

Solution 2:[2]

You can do that in your SQL query with using GROUP BY:

SELECT category, item FROM table_name GROUP BY category, item;

And you will get exactly what you want. A list of items grouped by category and avoid repeating items.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 CBroe
Solution 2 Ady

Related Questions