Grouping a SQL result set in PHP

Question

Say I have a query the following query run:

Edit

Added order clause because the real sql statement has one.

SELECT description, amount, id FROM table ORDER BY id

In this instance, the ID is not unique to the dataset. It would return something like this.

    Description    Amount    ID
    -----------    ------    --
1    Hats            45       1
2    Pants           16       1
3    Shoes           3        1
4    Dogs            5        2
5    Cats            6        2
6    Waffles         99       3

What I need to do is enclose each section of IDs in it's own div (So rows 1,2,3 in one div, 4,5 in another div and 6 in it's own div).

There are tons of solutions to this but I just can't think of one that isn't overly complicated.

I want to be able to keep the SQL how it is and somehow sort the data set in PHP so that I can loop through each section of the dataset while looping through the dataset as a whole.

Some kind of array would work but the structure of it is stumping me.

How can I get this to work? PHP solutions would be idea but theoretical will help too.

Answer 1

See if something like this works for you.

// execute query: Select description, amount, id from table

$results = array();

while ($row = $query_result->fetch_array()) {
    if (!isset($results[$row['id']])) $results[$row['id']] = array();
    $results[$row['id']][] = $row;  // push $row to results for this id
}

// later on
foreach($results as $id => $data) {
    // output div based on $id
    foreach($data as $datum) {
        // output individual data item that belongs to $id
    }
}