Displaying Scheduled Events

Question

I had this problem some years ago and back then I implemented a "different logic" in order to deliver the project but the doubt remains in my mind and hopefully with your help I'll be able to understand it now.

Suppose I have some scheduled events on my database that may or may not spawn over several days:

id    event           start          end
-----------------------------------------------
1     fishing trip    2009-12-15     2009-12-15
2     fishCON         2009-12-18     2009-12-20
3     fishXMAS        2009-12-24     2009-12-25

Now I wish to display the events in a calendar, lets take the month of December:

for ($day = 1; $day <= 31; $day++)
{
    if (dayHasEvents('2009-12-' . $day) === true)
    {
     // display the day number w/ a link
    }

    else
    {
     // display the day number
    }
}

What query should the dayHasEvents() function do to check if there are (or not) events for the day? I'm guessing SELECT .. WHERE .. BETWEEN makes the most sense here but I've no idea how to implement it. Am I in the right direction?

Thanks in advance!

---

@James:

Lets say we're on December 19th:

SELECT *
FROM events
WHERE start >= '2009-12-19 00:00:00'
  AND end <= '2009-12-19 23:59:59'

Should return the event #2, but returns nothing. =\

Answer 1

You're definitely on the right track. Here is how I would go about doing it:

SELECT *
FROM events
WHERE start <= '2009-12-01 00:00:00'
  AND end >= '2009-12-01 23:59:59'

And you obviously just replace those date values with the day you're checking on.

Answer 2

You should scratch that approach and grab all events for the given month up front so you only need to perform a single query as opposed to N queries where N is the number of days in the month.

You could then store the returned results in a multidimensional array like so:

// assume event results are in an array of objects in $result
$events = array();
foreach ($result as $r) {
    // add event month and day as they key index
    $key = (int) date('j', strtotime($r->start));
    // store entire returned result in array referenced by key
    $events[$key][] = $r;
}

Now you'll have a multidimensional array of events for the given month with the key being the day. You can easily check if any events exist on a given day by doing:

$day = 21;
if (!empty($events[$day])) {
    // events found, iterate over all events
    foreach ($events[$day] as $event) {
        // output event result as an example
        var_dump($event);
    }
}

Answer 3

James has the right idea on the SQL statement. You definitely don't want to run multiple MySQL SELECTs from within a for loop. If daysHasEvents runs a SELECT that's 31 separate SQL queries. Ouch! What a performance killer.

Instead, load the days of the month that have events into an array (using one SQL query) and then iterate through the days. Something like this:

$sql= "SELECT start, end FROM events WHERE start >= '2009-12-01' AND end <= '2009-12-31'";
$r= mysql_query($sql);
$dates= array();
while ($row = mysql_fetch_assoc($r)) {
  // process the entry into a lookup
  $start= date('Y-m-d', strtotime($row['start']));
  if (!isset($dates[$start])) $dates[$start]= array();
  $dates[$start][]= $row;
  $end= date('Y-m-d', strtotime($row['end']));
  if ($end != $start) {
    if (!isset($dates[$end])) $dates[$end]= array();
    $dates[$end][]= $row;
  }
}

// Then step through the days of the month and check for entries for each day:
for ($day = 1; $day <= 31; $day++)
{
    $d= sprintf('2009-12-%02d', $day);
    if (isset($dates[$d])) {
        // display the day number w/ a link
    } else {
        // display the day number
    }
}

For your purposes a better SQL statement would be one that grabs the start date and the number of events on each day. This statement will only work properly if the start column is date column with no time component:

$sql= "SELECT start, end, COUNT(*) events_count FROM events WHERE start >= '2009-12-01' AND end <= '2009-12-31' GROUP BY start, end";