generate weekly calendar in mysql/php

Question

i'm developing a read-only weekly calendar view of users's events. Columns are the days of the week (mon > sun) Rows are timeslots (8:00>9:00, 9:00>10:00... from 8AM up to 7PM)

Question: what's the best approach to generate that user calendar:

Option 1: pure SQL I don't even know if that is possible, but i would find it supremelly elegant: have mysql generate a table with 7 columns (the days) and 11 rows (the timeslots) and have subqueries for each timeslot, checking if there is a meeting booked for that timeslot/user.

option 2: php/mysql have mysql just retrieve the meetings booked/user, and cross it with a php-generated calendar.

Is option 1 at all possible? If it is, is it resource-intensive?

Thank you, Alexandre

UPDATE: QUERY SUGGESTIONS

Here is the "option 2" query i use to get the booked events (lessons in this case: the user is a teacher).

SELECT DISTINCT date, hour_from, hour_to, courses.description, courses.alias, teachers.name, locations.new_acronym
        FROM timetables
        INNER JOIN courses ON (courses.id=timetables.course_id)
        INNER JOIN teachers ON (teachers.id=timetables.prof_id)
        INNER JOIN locations ON (locations.id=timetables.location_id)
        WHERE ((timetables.prof_id='$id')
           AND (timetables.date >= '$starting_date')
           AND (timetables.date < date_add('$starting_date', INTERVAL 7 day))) ;

I'm very interested for suggestions on a query that would make option 1 work !

Answer 1

Trying to describe a calendar as a SQL table doesn't sound like a good match. It's certainly possible to perform a query that will generate a table with 7 columns and 11 rows, but then what would you do with that generated table? You'd still have to render it using PHP.

So start with the assumption that PHP is rendering a calendar, and do away with the extra work of making SQL generate one (and an extremely specific one!) beforehand. This way you can use a single SQL query to retrieve all the events that should be visible in one shot, and then place them on the calendar as you draw it.

Answer 2

i did something like that, and i think a table with 7 columns and 11 rows is a very bad solution - possible but bad.

Just one table, where you store all "events" and then render that with php. You Calender is WAY more flexible (imagine you want to add more hours of the day, or make it possible that half hours can be used, if you build it fine this will just be config settings).

If you still choose option 1: I dont think that it is very resource intensive, and i think you might be able to cache that stuff so it wont be to hard.

Answer 3

I´m guessing but I think you could do option 1 if you create a table with the timeslots. This will simplify a lot the queries. And the PHP code to generate the Calendar page will much simplier: you´ll need just to iteract over the result set. And, to get a good performance you should create apropriate index and key (i cant propose wihch ones beforehand).

EDIT TO ADD A SUGGESTION: @pixeline: Since you didnt add the table layouts, this is a shoot in the dark, bu i think just changing the INNER JOINs to LEFT OUTER JOIN you do it:

    SELECT DISTINCT date, hour_from, hour_to, courses.description, courses.alias, teachers.name, locations.new_acronym
    FROM timetables
    LEFT OUTER JOIN courses ON (courses.id=timetables.course_id)
    LEFT OUTER JOIN teachers ON (teachers.id=timetables.prof_id)
    LEFT OUTER JOIN locations ON (locations.id=timetables.location_id)
    WHERE ((timetables.prof_id='$id')
       AND (timetables.date >= '$starting_date')
       AND (timetables.date < date_add('$starting_date', INTERVAL 7 day))) ;

If timetables doesnt have all the hours for the day, you could create a table with them (say timeslot) and put it as the first table and put timetables as a LEFT OUTER JOIN or a CROSS JOIN. And the others as LEFT OUTER JOIN as above.