What's the best way to implement recurring events in FullCalendar via PHP/MySQL?

Question

I'm getting ready to use the JQuery-based FullCalendar in my online app using PHP/MySQL and noticed that when implementing recurring events, you must place a new item in the event array for each recurrence (using the same ID), like this:

events: [
    {
        id: 999,
        title: 'Repeating Event',
        start: new Date(y, m, d-1, 16, 0),
        allDay: false
    },
    {
        id: 999,
        title: 'Repeating Event',
        start: new Date(y, m, d+6, 16, 0),
        allDay: false
    },
    {
        id: 999,
        title: 'Repeating Event',
        start: new Date(y, m, d+13, 16, 0),
        allDay: false
    }
]

Okay, so that's not a big deal. Using MySQL, I'll just feed in the event looped a bunch of times, like maybe 100 in the future from the start date of that event if it doesn't have an end date. But now I'm loading up the page with a bunch of JavaScript that might not even be needed (if the user just opens the calendar to see one month). Not cool.

There has to be a better way... does anyone have their own experience with this?

Answer 1

FullCalender will by default fetch events for the current time-frame using lazyFetching. Generally, this means you'll only send-off an ajax call when the user switches out the current month. You can reduce server load by turning off caching:

$('#calendar').fullCalendar({
    events: {
    url: '/myfeed.php',
    cache: true
  }
});

Further, you should optimize your SQL on the server to only fetch events in a given timeframe:

FullCalendar will determine the date-range it needs events for and will pass that information along in GET parameters. ... Here is a URL that FullCalendar might visit:

/myfeed.php?start=1262332800&end=1265011200&_=1263178646

If you use these get parameters in your SQL, you'll drastically reduce the data you need to send to the client:

SELECT *
FROM events
WHERE start > PARAMS_START_DATE AND end < PARAMS_END_DATE

Obviously, this won't be as succinct when using recurring events, you may for example (in pseudo-code):

recurring_events.each do |recurring_event|
  running_date = recurring_event.starts_on
  while running_date < PARAMS_END_DATE
    if running_date > PARAMS_START_DATE
      events.push { :event => event.id, :date => running.date, ... }
    end
    running_date = running_date + recurring_event.frequency_as_days
  end
end

This way, you'll just send back to full calendar what is applicable for the current view.