2

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']];
    }
}
GrumpyCrouton
  • 8,486
  • 7
  • 32
  • 71

2 Answers2

3

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']
JParkinson1991
  • 1,256
  • 1
  • 7
  • 17
  • Thanks for the code snippet, which may give me some immediate help, but I prefer to _learn_ what I did wrong, and how to fix my current code, or at least to learn how your snippet actually works by explanation. I can understand your code, but future readers may not be able to. – GrumpyCrouton Sep 29 '17 at 13:55
  • No problem, i have added in a few more comments around the code, have a read and let me know if you need any other pointers. – JParkinson1991 Sep 29 '17 at 14:00
  • You have a syntax error here `if(!array_key_exists($value['group'], $grouped){`, you are missing and ending parenthesis. Also, I was able to condense it from 9 lines of code to 4 lines of code https://pastebin.com/jMbEF9PV for any future readers – GrumpyCrouton Sep 29 '17 at 14:22
  • Fixed the syntax error. For your compression (i wouldnt class it as condensed, the code is the same just formatted differently) i would recommend to keep your code as readable as possible. When you come back to it months down the line it will be easier to pick up again. If you are worried about minified code for production this can always be done. For me though i would always recommend a heavily commented, readable source for development. – JParkinson1991 Sep 29 '17 at 14:27
  • I agree for the most part with the readability of code, but for this particular case it's really just fuelling a dropdown box and I don't expect it to ever change. I'm also the only PHP developer on the project, and I can read this code fine :) – GrumpyCrouton Sep 29 '17 at 14:29
  • Whatever works best for you! Always keep in mind the developer who's going to pick up the code after you move on though haha ;) Im sure we've all fallen foul to a previous developers work in our jobs. – JParkinson1991 Sep 29 '17 at 14:30
1

I've needed this again recently, so I made a function based around @JParkinson1991's answer.

I'm putting this here for documentation, and to possibly help future readers.

function groupArray($arr, $group, $preserveSubArrays = false, $preserveGroupKey = false) {
    $temp = array();
    foreach($arr as $key => $value) {
        $groupValue = $value[$group];
        if(!$preserveGroupKey)
        {
            unset($arr[$key][$group]);
        }
        if(!array_key_exists($groupValue, $temp)) {
            $temp[$groupValue] = array();
        }

        if(!$preserveSubArrays){
            $data = count($arr[$key]) == 1? array_pop($arr[$key]) : $arr[$key];
        } else {
            $data = $arr[$key];
        }
        $temp[$groupValue][] = $data;
    }
    return $temp;
}

Breakdown

function groupArray($arr, $group, $preserveGroupKey = false, $preserveSubArrays = false)

This function accepts 2 to 4 parameters.

  1. The flat array you want to group (array)
  2. The key you want to group by (string/int)
  3. Option to preserve the group key in the output of each sub array (Boolean)
  4. Option to preserve sub arrays. If only 1 key exists in each sub array, the function will store just the single value for each row instead of an array (Boolean)

The first parameter is the array itself, the second parameter is the key that you want to group by, and the 3rd (optional) parameter is a boolean that tells the function if you want to preserve the group key in the sub arrays.

$temp = array();
foreach($arr as $key => $value) {
    $groupValue = $value[$group];
    if(!$preserveGroupKey)
    {
        unset($arr[$key][$group]);
    }
    if(!array_key_exists($groupValue, $temp)) {
        $temp[$groupValue] = array();
    }
    $temp[$groupValue][] = $arr[$key];
}

First, we create a temporary array called $temp

Next, we loop through the array grabbing the key (Which should be a string or int), and the value (Which should be an array).

We set $groupValue to whatever the value is of the $group you chose, such as "group" in the example below.

$arr = [
    0 => [
        "group" => "group1",
        "name" => "Bob",
    ],
    1 => [
        "group" => "group1",
        "name" => "Randy",
    ],
    2 => [
        "group" => "group1",
        "name" => "Susan",
    ],
    3 => [
        "group" => "group2",
        "name" => "Larry",
    ],
    4 => [
        "group" => "group2",
        "name" => "David",
    ],
    5 => [
        "group" => "group3",
        "name" => "Perry",
    ],
];

Then we check if we want to $preserveGroupKey's. If this boolean is false (And it is by default), the key will be removed leaving several subarrays with just the "name" key left.

Now we check if the $groupValue exists in our $temp array, if it does not, we create it.

Then we add to the $temp[$groupValue] whatever the current row values are. From the example above, we would end up with:

Array
(
    [group1] => Array
        (
            [0] => Bob
            [1] => Randy
            [2] => Susan
        )

    [group2] => Array
        (
            [0] => Larry
            [1] => David
        )

    [group3] => Array
        (
            [0] => Perry
        )

)

Or, with the 3rd parameter set to true you would get:

Array
(
    [group1] => Array
        (
            [0] => Array
                (
                    [name] => Bob
                )

            [1] => Array
                (
                    [name] => Randy
                )

            [2] => Array
                (
                    [name] => Susan
                )

        )

    [group2] => Array
        (
            [0] => Array
                (
                    [name] => Larry
                )

            [1] => Array
                (
                    [name] => David
                )

        )

    [group3] => Array
        (
            [0] => Array
                (
                    [name] => Perry
                )

        )

)
GrumpyCrouton
  • 8,486
  • 7
  • 32
  • 71