将多维数组转换为“单个”数组

Question

I'm making a calendar in PHP and I need to count all the events for each day of the year, in order to display the tiles properly (ex: green for few events, orange for 3-4 events and red for more than 5 events).

Here's the query that I use to get all the days with events, and the number of events in that day:

$q1 = $pdo->query("SELECT day, COUNT(*) as number FROM events GROUP BY day");

With three events in the DB, and two on the same day, this is the output:

array(2) {
  [0]=>
  object(stdClass)#8 (2) {
    ["day"]=>
    string(3) "145"
    ["number"]=>
    string(1) "1"
  }
  [1]=>
  object(stdClass)#9 (2) {
    ["day"]=>
    string(3) "335"
    ["number"]=>
    string(1) "2"
  }
}

When I'm echo -ing the days, I keep track of the $currentDay variable, which starts at 1 and will end at 365 once the last day is echo 'ed.

I would like to get the value of number for each of the days, but I don't know how to do that when the output is a multi-dimensional array like that.

Is there a way to convert that array into something more useful, where I could find the number of events (number) by doing something like this:

$array[$currentDay]["number"] = $number; 

If not, what is the best approach to keep the number of SQL queries to the strict minimum? I don't want to end up fetching the events for a day once per day, 365 queries for that is madness. I'm not sure what's the best approach to take in this case.

EDIT

To output the days, I start from 1 and go to 12 in a for (the months), and for each month, I get the number of days, so it accounts for the leap years with one more day.

The reason I'm using a day field in my events table is because I tried to keep things simple. There's also a startTime and endTime field, both Integer's to store the timestamps of the start and end of the event, although I don't know how to count the number of events of the day when using timestamps like that.

Answer 1

I think the easiest way is just a simple loop:

// Initialize $q1 as you did..
$q1 = ... get stuff from DB

$q2 = array();
foreach ($q1 as $row) {
  $q2[$row['day']] = $row['number'];
}

// At this point, $q2 is the array you want to have.

Although, if you want to output just the numbers, you can just write the loop without the extra array.

foreach ($q1 as $row) {
  echo "number for ".$row['day']." is ".$row['number']."<br>";
}