将平面数组转换为按类别分组的数组

Question

I've got a database table that looks like this:

uid | group  | category
1   | group1 | cat1
2   | group1 | cat2
3   | group2 | cat3
4   | group2 | cat4
5   | group2 | cat5
6   | group3 | cat6
7   | group3 | cat7

But I need this data in an array, that groups categories by their group.

For example, my array should look like this:

Array
(
    [group1] => Array
        (
            [0] => Array
                (
                    [0] => 1
                    [1] => cat1

                )

            [1] => Array
                (
                    [0] => 2
                    [1] => cat2
                )

        )

    [group2] => Array
        (
            [0] => Array
                (
                    [0] => 3
                    [1] => cat3
                )

            [1] => Array
                (
                    [0] => 4
                    [1] => cat4
                )

            [2] => Array
                (
                    [0] => 5
                    [1] => cat5
                )

        )

    [group3] => Array
        (
            [0] => Array
                (
                    [0] => 6
                    [1] => cat6
                )
            [1] => Array
                (
                    [0] => 7
                    [1] => cat7
                )

        )

)

I've written a foreach loop that does just this, but I have a problem.

My problem is that it always leaves out the very last row of the table, and I'm not sure how to fix it. In my mind, the logic dictates that it should always work.

I was thinking that after the loop I could just add the very last row to the new array, but I think that may cause issues if the last row has a different group, and I would rather the solution be built into the foreach loop.

Unfortunately, I am at a loss here. How can I fix my code to include the very last row of the database query?

I would also be interested to see what improvements I can make on my current code, but that may be a better question for codereview.

My loop:

$pass = [];
foreach($stmt as $key => $value) {
    if(empty($currentGroup)) $currentGroup = $value['group'];
    if(empty($temp)) $temp = [];
    if($currentGroup != $value['group'] || $key+1 == count($stmt)) {
        $pass[$currentGroup] = $temp;
        $currentGroup = $value['group'];
        $temp = [];
        $temp[] = [$stmt[$key]['uid'], $stmt[$key]['category']];
    } else {
        $temp[] = [$stmt[$key]['uid'], $stmt[$key]['category']];
    }
}

Answer 1

The following should do it:

<?php

//Create an array to store our grouped rows
$grouped = array();

//Loop over all rows returned by the $stmt that has been executed.
//You could probably remove the key from here, it's not needed it seems.
//The keys within the $value array will match the names of the columns in 
//the database,
foreach($stmt as $key => $value){

    //As we're storing by the group value from the row we first want to
    //check if our grouped array contains a key for the group of the row
    //being processed. If it does not, create an empty array within the
    //grouped data for this group.
    if(!array_key_exists($value['group'], $grouped)){
        $grouped[$value['group']] = array();
    }

    //Knowing we will always have an array element for the rows group
    //we can blindly append the values for this row to the grouped 
    //container using its values.
    //'[] =' is just short hand append.
    $grouped[$value['group']][] = array(
        $value['uid'],
        $value['category']
    );
}

Hope that helps!

---

To further future proof this loop you could change the grouped value append to the following:

<?php

//Setting the whole row (minus the group) rather than just the uid 
//and category explicitly allows this code to work without modification
//as the datatable changes, ie. new columns. Assuming that is the 'group'
//column remains present
unset($value['group']);
$grouped[$value['group']][] = $value;

Grouped contents data could now be accessed using the following:

<?php

//Acceess data via column name not array index, yay!
echo $grouped['group1']['uid']