PHP Hourly Calendar, Running through timestamps stored in database.

Question

I have a MySQL table titled 'my_cal' with a column titled 'start_time' for particular events in the calendar.

The way items are added to the database, there are no overlapping events. There also may not be an event for a particular day/time either, in this case we leave it blank or put a blank value.

My problem lies in the fact that running 00:01AM to 23:59PM for each day sequentially would be easy, but I want to get the data as:

Mon 00:00, Tues 00:00, Wed 00:00, etc, etc

Mon 01:00, Tues 01:00, Wed 01:00, etc, etc

Mon 02:00, Tues 02:00, Wed 02:00, etc, etc

This is to build a table with 25 Rows (24 hours of the day + 1 for title), and 8 columns (7 days of the week + 1 hourly title)

Here is an example of how the calendar should look as far as HTML Table structure goes: http://www.wmnf.org/programs/grid

I have made an attempt at getting this to work, Running through the MySQL results, and saving them as $data[$hour_of_the_day][$day_of_the_week]

I would then have recursive while() loops for each hour, and then each day,

$hour=0;
while($hour > 24)
{
    $day=0;
    while($day > 8) 
    { 
        if(isset($data[$hour][$day])) 
        { echo "event"; } 
        $day++;
    }
    hour++;
}

I believe this may get me by, but it does not seem very elegant.

I also wanted the time the calendar started to be 6:00AM, and not 12:00AM. I wanted the first day listed to be today - 3 (to center the calendar on today)

I.e:

$calendar_begin_day = date("j", mktime()) - 3;

I am looking for any way to make this easier, as I want it to run as efficient as possible due to large hit count.

Thanks

Answer 1

Hello, When you retrieve your results, I think you can use the ORDER BY clause and date functions on your to order them by hour and then by day and not the other way round. This way you can iterate on results and avoid storing your data in a php array.