PHP & MYSQL: using group by for categories

Question

My database has the following setup

productid | productname | category id

and I want to output them like so:

category #1
item 1 
item 2
item 3

category #2
item 1 
item 2
item 3

I used group by the group them together and that works fine, but I want to loop through each group and display the contents of that group. How would I do this?

Answer 1

I'd recommend just a simple query to fetch all the rows, sorted by category id. Output the category only if its value changes from the previous row.

<?php

$stmt = $pdo-> query("SELECT * FROM `myTable` ORDER BY categoryID");

$current_cat = null;
while ($row = $stmt->fetch()) {
  if ($row["categoryID"] != $current_cat) {
    $current_cat = $row["categoryID"];
    echo "Category #{$current_cat}\n";
  }
  echo $row["productName"] . "\n";
}

?>

Answer 2

This should work:

$categories = array();
$result= mysql_query("SELECT category_id, product_name  FROM `table` GROUP BY `catagory_id` ORDER BY `catagory_id`");
while($row = mysql_fetch_assoc($result)){
    $categories[$row['category_id']][] = $row['product_name'];
}

// any type of outout you like
foreach($categories as $key => $category){
    echo $key.'<br/>';
    foreach($category as $item){ 
        echo $item.'<br/>';
    }
}

The output you can style yourself. You simply add everything into a multidimensional array with the category id as the first level keys.

EDIT: The resulting array might look like this:

$categories = array(
    'cateogy_id_1' => array(
        1 => 'item_1',
        2 => 'item_2',
        ...
    ),
    'cateogy_id_2' => array(
        1 => 'item_1',
        2 => 'item_2',
        ...
    ),
    ....
);

Answer 3

What you want is to order them by the category, not group them.

SELECT * FROM MyTable
ORDER BY category_id, product_id

When you iterate through the list, just output a new header whenever the category_id changes.

Answer 4

This approach does not need the data to be sorted by category_id.

You can use php to group each category together in a nested array. After this, the data can be easily be displayed in a table, passed to a grap/chart library, etc...

<?php
$stmt = $pdo-> query("SELECT * FROM myTable ORDER BY categoryID");


$categories = []; //the array to hold the restructured data

//here we group the rows from different categories together
while ($row = $stmt->fetch())
{
    //i'm sure most of you will see that these two lines can be performed in one step
    $cat = $row["categoryID"]; //category id of current row
    $categories[$cat][] = $row; //push row into correct array
}

//and here we output the result
foreach($categories as $current_cat => $catgory_rows)
{
    echo "Category #{$current_cat}\n";

    foreach($catgory_rows as $row)
    {
        echo $row["productName"] . "\n";
    }
}
?>