Advanced Event Calendar

[jcombs_31]

I've created some pretty simple event calendars that output a monthly table and query the database for daily events.  I'm looking to create a much more advanced calendar like you would find in a desktop app like outlook.  I need recurring events and floating events and probably times because people will be submitting requests for schedules.

 

I'm really just trying to wrap my head around the basic database design to handle the floating and recurring events.  I don't to be limited to a weekly recurrence pattern.  I would like options like daily, weekly, monthly, or maybe even biweekly, etc. How do you account for a recurring event that has no end date in the database?

 

Maybe this is something easy to create, but I would like some direction on the database schema that would work best to help get me going in the right direction.

 

Any thoughts or references on this would be very helpful.

[paralyzah]

RecurringEvents: Key, Pattern, Date

RecurringEventsExceptions: ForeignKey, Date

 

In RecurringEvents, Pattern is a TINYINT where 1 = Weekly, 2 = Every second week, 3 Monthly

Date is if Pattern is 1 or 2 the day of the week, and if Pattern is 3 the day of the month.

 

In RecurringEventsExceptions, Date is an excact date that match a recurring event and means that the user have moved it (and you've stored it as a regular event this week or month or whatever) or that the user has deleted it this week or month or whatever.

 

You'd probably need some other fields in the tables for other purposes, but these are the ones you asked for.

[jcombs_31]

I probably should have worded better what I'm after.  I do want a good idea about data design, but let's say for example I did something like this:

 

TABLE: EVENTS

 

id

event_start  [date]

event_end  [date]

recurrence  [enum]  {once, daily, weekly, monthly, yearly}

title [varchar]

description [varchar]

 

 

The only things I'm concerned about are the dates.  Floating events can be queried pretty easily by the start/end dates, but how would you really be querying recurrence?

 

The calendar itself is basically created by a loop and on each day I would check if an event(s) exist and display it.  I'm really looking for a little logic on how this would work best.

 

[paralyzah]

What your asking for is exactly what I answered in my previous post!? You'll need the two tables I gave you in addition to the table where you store the regular events.

[paralyzah]

If you're asking how to get and sort the data, I'd be using two queries. One to get all the regular events and one to get the recurring events. Then I'd have to convert the recurring events to actual dates for a specific time frame (i.e. from now and three months forward in time). Finally I'd store all the dates in one array and sort them.

[448191]

Perhaps this topic will help you: http://www.phpfreaks.com/forums/index.php/topic,227128.0.html

 

Just need to squeeze in an end date and it should drop in nicely I think.

[jcombs_31]

Thanks John, that's a hell of a query.  Points me in the right direction.