CODEWITHSUNDEEP
phparraysmultidimensional-arraygroupingresultset
nickifrandsen
nickifrandsen

Reputation: 433

Group query result set rows by month name

I really need some help to figure this one out.

I've got a number of arrays and i would like to merge some of them. It looks like as following.

while ($row = $db->fetch_array($result_set)) {
    $year           = $row['year(Tidspunkt)'];
    $month_num      = $row['month(Tidspunkt)'];
    $month      = $cal->name_of_month($row['month(Tidspunkt)']);
    $type           = $row['Klubtype'];
    $visits         = $row['count(Handling)'];
    $days_in_month = $cal->days_in_month($month_num,$year);
    $avg           = $visits / $days_in_month;
    $object_array[]= array('month' => $month , 'visits' => $visits, 'type' => $type, 'avg' => $avg);
}
return $month_array;

And the output looks like this

Array ( 
     [0] => Array ( [month] => Januar [visits] => 891 [type] => FK [avg] => 28.7419354839 ) 
     [1] => Array ( [month] => Januar [visits] => 23 [type] => UK [avg] => 0.741935483871 ) 
)

Now I would like to merge these two arrays based on the value of month. Imagine when I've got arrays for a whole year. Then it would be nice to have 12 arrays instead of 24.

Upvotes: 0

Views: 2598

Answers (2)

mickmackusa
mickmackusa

Reputation: 48071

I recommend performing all of the calculations in the SQL layer using GROUP BY and aggregate functions.

While iterating the result set, push reference variables into the result array for each unique month. Then you only need to push your row data as a new child of the reference which is identified by the month value.

PHPize Online Demo

$danishMonths = [
    1 => 'Januar',
    2 => 'Februar',
    3 => 'Marts',
    4 => 'April',
    5 => 'Maj',
    6 => 'Juni',
    7 => 'Juli',
    8 => 'August',
    9 => 'September',
    10 => 'Oktober',
    11 => 'November',
    12 => 'December'
];

$sql = <<<SQL
SELECT 
    YEAR(Tidspunkt) year,
    MONTH(Tidspunkt) month_num,
    Klubtype type,
    COUNT(Handling) visits,
    COUNT(Handling) / DAY(LAST_DAY(MIN(Tidspunkt))) avg_visits
FROM visits
WHERE YEAR(Tidspunkt) = '2024'
GROUP BY year, month_num, type
ORDER BY year, month_num, type
SQL;

$month_array = [];
foreach ($mysqli->query($sql) as $row) {
    if (!isset($ref[$row['month_num']])) {
        $month_array[] =& $ref[$row['month_num']];
    }
    $ref[$row['month_num']][] = [
        'month' => $danishMonths[$row['month_num']],
        'visits' => $row['visits'],
        'type' => $row['type'],
        'avg' => $row['avg_visits']
    ];
}
var_export($month_array);

With my fake sample data, this is the structure of the output:

array (
  0 => 
  array (
    0 => 
    array (
      'month' => 'Januar',
      'visits' => '3',
      'type' => 'FK',
      'avg' => '0.0968',
    ),
    1 => 
    array (
      'month' => 'Januar',
      'visits' => '2',
      'type' => 'UK',
      'avg' => '0.0645',
    ),
  ),
  1 => 
  array (
    0 => 
    array (
      'month' => 'Februar',
      'visits' => '2',
      'type' => 'FK',
      'avg' => '0.0690',
    ),
    1 => 
    array (
      'month' => 'Februar',
      'visits' => '1',
      'type' => 'UK',
      'avg' => '0.0345',
    ),
  ),
)

Upvotes: 0

alex
alex

Reputation: 490647

I think this may be what you want...

$newArray = array();

foreach($array as $value) {

   $month = $value['month'];
   unset($value['month']);
   $newArray[$month][] = $value;
}

This will give you something like...

Array ( 
     ['Januar'] => Array (
        [0] => Array( [visits] => 891 [type] => FK [avg] => 28.7419354839 ) 
        [1] => Array ( [visits] => 23 [type] => UK [avg] => 0.741935483871 ) 
)

Upvotes: 1

Related Questions