Creating recurring calendar events in PHP/MySQL

Create three tables with a structure like:

event table

-id
-schedule_type
-schedule_id
-title
etc.

schedule table

-id
-event_id
-datetime

schedule_recurring table

-id
-event_id
-date
-time

In your event table, the schedule_type field will be either 0 or 1, which would indicate to the application which table the scheduling information is stored in, as well as how to interpret that information.

A non-recurring event will be stored in schedule with a datetime: 2011-09-06 00:00:00, and recurring events will be stored in schedule_recurring with a date: 'every 1st Monday' and a time: 09:30,12:20 (If the event occurs twice on every first Monday of the month).

Maybe this will help get you started!

I know this is an old post but I was thinking the same thing too.

I like the persons solution about using multiple tables to do it, but in fact it can all be done from one table.

Create a table with the following data...

event_title
event_text
event_image
and_other_fields_about_event
recur_code (text)
recur_mode (integer)
start_date (date)
end_date (date)
recur_end_date (date)

recur_mode can have three states - 0 = no recurrence 1 = recurrence with end date 2 = ongoing with no end date (e.g. if you want to add something like 1st Jan as New Years Day)

recur_code would store either NULL or a code in it if the date recurs. The code that should be used there would be the PHP DateInterval code (i.e. P1Y for 1 year or P3M (3 months) or P7D (7 days), etc) - if you want to shrink the data a bit you could chop off the initial 'P' and add it back later as the initial 'P' is always going to be P, it stands for Period so "Period 3 Months" "Period 7 Days", etc.

Then when your retrieving data from the database - you retrieve all data with the following searches

( end_date >= CURDATE () ) OR ( ( recur_mode = 1 ) AND ( recur_end_date >= CURDATE () ) ) OR ( recur_mode = 2 )

(please note this isn't proper SQL - it's just a basic example of the or statement you'd need)

then once you've retrieved the data use PHP with a while loop and DateInterval to increase the start_date until you get to the next re-occurrence also making sure that if recur_mode is set to 1 the start date is not after the recur_end_date.

All done in one table - and also if you want an input form to put the code in then use a hidden field with the dateinterval value in whilst using various radio buttons to select the interval - then use jQuery onchange to update the hidden value with the new selector values.