Reoccuring dates on custom calendar

[tektipsismyfavorite]

I built a custom calendar and i've successfully been able to list out events on it based on the date (YYYY-MM-DD), however my next obstacle is allowing events to re-occur more than once. For instance, if something needs to happen the 3rd Friday of every month. Or repeating an event on the 15th of every month. Or every other day or every other week.

I was just curious if anyone has ever tackled this obstacle and wondered how is it accomplished. I store my events in a single table and the occurance of the event is stored as a DATETIME field (in MySQL).

My original idea was to use 0000 as the year for items that re-occured yearly. And 00 for month/day for events that occured every month on X day or every month on a certain day of a certain week. I'm not entirely sure how to implement that, but once I establish what system I'm going to use, the other issue is inserting that event into my array which already holds all the events for that particular month. and it would need to be inserted at the right spot (in order) so when it came time to output that day, it was in the right place.

Any ideas are helpful! Thanks.

 

[jpadie]

off the cuff i think the structurally easiest solution would be to store each occurrence of an event as an explicit event on the relevant date. i'd add another field to the event database with a grouping identifier.

i'd then keep a separate table of recurring events with the recurrence rules and the id used to identify the group.

for changes to the reoccurrence rules i would delete all events with the groupid and then reinsert new values UNLESS:
1. the event was in the past; OR
2. the event had been singularly changed (as in a weekly 9am meeting had, on a specific day, been shifted to 10am). to allow for this you would store another column in the database indicating that a grouped occurrence had been altered. then in the admin script you could give the admin the ability to override the normal rule that would leave this entry alone.

for ad infinitum recurrences you need to impose some form of arbitrary endpoint otherwise your record table grows to a silly size. i'd suggest a year. you can always build a cron job to extend the recurrence each month or something.

 

[tektipsismyfavorite]

awesome. Thanks. I had thought of the cron job idea, but how do suggest explaining to the user? Ie. Please don't expect recurring events to show up past 2 years in the calendar because they haven't been created yet... ?

Thanks again.

 

[jpadie]

why not hide it from the use. if the user requests a detailed view of his calendar for a period, before serving the view, check that you don't need to extend any recurrences (and action if you do need). you can probably do this in place of a cron jo: the logic being that if the user wants to look at his calendar that far out, there is a chance that the recurrences in that period are useful data to him/her whereas someother people don't care more than a week out.