PHP calendar - Best way to get and display events

[Drown]

Hello,

 

I'm making a PHP calendar application. It's very simple, I take a year, iterate through the months, display a tile for each day (so 365 tiles spreaded across twelve calendars) and I would like to color the day's tile depending on the number of events planned that day. For example, a day with only 1-2 events would be green, one with 3-4 yellow, and one with 5 or more events would be displayed in red.

 

Here's the code that I use to display the calendar : 

$year = 2015;for($month = 1; $month <= 12; $month++){	//Get the infos on that month	$numDays = cal_days_in_month(0, $month, $year);	$firstDay = date("w", strtotime($year . "-" . $month . "-1")); //For offset	$lastDay = date("w", strtotime($year . "-" . $month . "-" . $numDays)); //For offset	//Display the offset days	for($i = 0; $i < $firstDay; $i++)	{		echo '<div class="day offset-day"></div>';	}	//Display regular days	for($j = 1; $j <= $numDays; $j++)	{		echo '<div class="day">' . $j . '</div>';	}	//Display offset days	for($i = 0; $i < (7 - $lastDay); $i++)	{		echo '<div class="day offset-day"></div>';	}}

The code above works as expected, the problem is when I try to color the days depending on the number of events.

 

This is how the events are stored in the databse : 

id - int (11)name - VARCHAR(255)startingTime - int(11)endingTime - int(11)

I use the startingTime and endingTime columns to store the timestamps for the beginning and the end of each event.

 

Since it's madness to make one query per day, to fetch all the events of that day, I fetch all my events for that specific year with one query, before the calendar is outputted. Here's the query that I use : 

SELECT startingTime, endingTime FROM events WHERE startingTime >= :yearBegin and startingTime <= :yearEnd

Where yearBegin is the timestamp for January 1st of that year, and yearEnd the timestamp for December 31st. 

 

This returns an array like this :

array(3) {  [0]=>  array(4) {    ["startingTime"]=>    string(10) "1442448000"    [0]=>    string(10) "1442448000"    ["endingTime"]=>    string(10) "1442448000"    [1]=>    string(10) "1442448000"  }  [1]=>  array(4) {    ["startingTime"]=>    string(10) "1441756800"    [0]=>    string(10) "1441756800"    ["endingTime"]=>    string(10) "1441756800"    [1]=>    string(10) "1441756800"  }

Finally, here's the problem. What is the best way to get the information that I want (number of events per day) with that information? I have no clue how I can properly iterate through that and get the informations that I want. 

 

I'm open to changing the DB structure if needed, and to take a completely different approach towards the handling of the events. At the moment I struggle to find an effective solution.

 

Thank you all for your help, let me know if you need any more informations.

[SSL]

Why aren't you using the Date format to store...dates? That would allow you to use GROUP BY and COUNT to get the number of evens for each day in one query.