CODEWITHSUNDEEP
phpsqlarraysloops
Will
Will

Reputation: 723

Grouping a SQL result set in PHP

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.

Upvotes: 4

Views: 708

Answers (4)

orourkek
orourkek

Reputation: 2101

Assuming associative arrays for the db results:

$final = array();

foreach($results as $result)
{
    $final[$result['id']][] = $result;
}

This leaves you with an associative array $final that groups the entries by ID

Upvotes: 1

drew010
drew010

Reputation: 69937

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
    }
}

Upvotes: 4

mellamokb
mellamokb

Reputation: 56769

A simple serial solution might look something like this:

$curId = '';       // track working id
$firstDiv = true;  // track if inside first div

// open first div
echo '<div>';

// foreach $row
{
    // when id changes, transition to new div, except when in first div
    if ($row->$id != $curId) {
        if ($firstDiv) {
            $firstDiv = false;
        } else {
            // start new div
            echo '</div>';
            echo '<div>';
        }
        $curId = $row->$id;  // track new current id
    }

    // display contents of current row
}

// close last div
echo '</div>';

Upvotes: 3

Brandon S
Brandon S

Reputation: 155

Just store the id in temp variable, if the next one is different close the div and open new div

Upvotes: 1

Related Questions