Reputation: 5982
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.
Upvotes: 0
Views: 95
Reputation: 116200
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>";
}
Upvotes: 2