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']];
}
}
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']
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.
- The flat array you want to group (array)
- The key you want to group by (string/int)
- Option to preserve the group key in the output of each sub array (Boolean)
- 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
)
)
)
