Group query result set rows by month name

Question

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.

alex · Accepted Answer · 2011-01-24 00:44:46Z

1

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

answered Jan 24, 2011 at 0:44

alex

492k205 gold badges890 silver badges992 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nickifrandsen Over a year ago

It does the job but I'm concerned about having the month value as "key" and not as "value". If you know what I mean?

alex Over a year ago

@nickifrandsen Why does it concern you? If it is not the key, then you will need some sort of unique key, which could be the numeric equivalent, e.g. January => 1.

nickifrandsen Over a year ago

It concern me because I would like to echo out the month. I don't know if it makes any sense. However I know i will doing a similar thing later on which makes it useful to have the e.g "month" as key and "January" as value. Like this 'month' => 'January'. I hope this explains my minor concerns.

alex Over a year ago

@nickifrandsen You can echo the month by referencing the key, e.g. foreach($months as $month => $value) { ... }. You can echo $month to get the month.

mickmackusa · Accepted Answer · 2024-11-18 02:07:48Z

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',
    ),
  ),
)

Collectives™ on Stack Overflow

Group query result set rows by month name

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related