Reoccurring events challenge

[dadamssg]

I have a database table that has events(title, description, location, start and end datetimes) stored to display in a calendar. I want the ability for a user to specify an event that reoccurs. I'm trying to wrap my head around the best way to achieve this is. My PHP script cycles through the days of a month to produce the table cells for the month and then queries the database if the day it's cycling through has an event.

 

I'm not sure how i should put the reoccurring events in the table. I figure i need to add some other fields to the table to capture the reoccurrence pattern the user specifies. ANOTHER thing i'm trying to figure out is the fact that if one of those reoccurring events needs to be changed(like the location). How the heck i should arrange that.

 

Anyone have any suggestions on how i should set up my table and query the reoccurring events? This is the query i have that pulls up the events for each day.

 

$quer = "SELECT * FROM Events WHERE '$day' BETWEEN date(start) AND date(end) AND username = '$username' ORDER BY start ASC";

[jdavidbakr]

I'd suggest looking at a calendar application you want to emulate and seeing the fields that they use to control this behavior (in the UI, not necessarily in the database).  Like in iCal, you set the frequency of the event and when it ends.  When you edit an event it breaks it into two or more events (depending on if you choose 'only this occurrence' or 'all future occurrences').  Then think about how you can structure your tables to accomplish this same data.  Obviously the database schema and the logic involved to display the calendar would be more complex than if you just have a single table of events, but it should be a good exercise in building relational tables    But, depending on how much control you want to give the user, it could get very complicated very quickly.

[brianlange]

What does recurring mean? Can it be every day, every hour, once a month, every third wednesday of the month?

[dadamssg]

yes...like every wednesday or the 1st of every month or the first friday of every month...along those lines.