Recurring Events – Server Side

Most people tend to make recurring events far more complicated then they need to be. In the case of my imaginary job scheduling calendar I have one MYSQL table to store events. In the table structure there is one “repeat” field defined as “text.” The reason for using text will become apparent as we walk through the framework for recurring events;

The repeat field stores a string that defines the recurrences and exceptions to it. Structurally the string is a series of characters much like a binary masks in sections delimited with semi-colons “;” and looks something like this;

  • J F M A M J J A S O N D;      month(s)
  • 1 2 3 4 L;      weeks of the month
  • S M T W T F S; days of the week
  • 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1; dates of the month
  • D W M; interval unit of days, weeks, or months
  • 999; number of interval units
  • D D; whether using day of week or dates of month
  • start date;
  • end date;
  • first exception date;
  • …..
  • last exception date

With PHP it is easy to explode this string into a array of sections and finally use split_str to blow each section into the arrays like moy, dom, wom, dow and so forth. In general the repeat matching is rather straight  forward except for determining the last day of week in a month.

matchRepeat($date, $repeat)

$date in repeat range of start - end
  $date is not an exception date.
    pattern && month matches
      day and not date && week of month matches && day of week matches true
      date and not day && date of month matches true
    interval (using DateTime and DateInterval)
     days    days % number == 0 true
     weeks   (days / 7) % number == 0  && days of week matches true
     months  months % number == 0
        day not date && week of month matches && day of week matches true
        date and not day && date matches true

Responding to the request for events;

1 Do a database query for requested events

SELECT * FROM `events` where `start` >= $start AND `end` <= $end AND `repeat` IS NULL;
foreach($event in $events)
     $result[]= calendarEvent($event);

2 Do a database query for recurring events;

SELECT * FROM `events` where `repeat` IS NOT NULL;       
foreach($event in $events){
            $test = $start;
            while ($test < $end)
                  if(matchRepeat($test, $event->repeat)
                          $result[] = calendarEvent($event->id); // all will have same id
                  $test += 86400;


3 Get holiday events;


4 Output events for FullCalendar;

header("Content-Type: application/json; charset=UTF-8");
echo json_encode($result);

5 Handling Recurring Event Updates and Changes;

$id = $_REQUEST['id'];
$start = $_REQUEST['start'];
$end = $_REQUEST['endt'];
$event = new event($id); //fetch event from database;
if($event->repeat && $event->start !== $start){
       $cloned = event::spawn($event, $start, $end); // new event with its own unique id
       $event->repeat += ';' . $cloned->start; //record exception


Comments are closed.