Calendar/Event Query!! Pls Help

[Forri]

Hi all

I am building a web calendar; storing events with a title, description, a location, the User locked in etc;

The event has a feature of recurrency and this is where my problem is!

I have 2 tables:

calendar_events
ID - a unique identifier
date - a start date
time - a start time
duration - duration of the event
repeat - a char (Y/N)
access - a char (Private/Public)
title - an event title
description - an event description
user_id - current user

calendar_events_repeat

event_id - the Parent Event ID
type - daily, weekly or yearly recurrence
end - end date - if any
frequency - occuring every x days
days - char that stores on which day the event is happening on recurrence usually holds something like: yynnnnn where this states that it recurs every sunday and monday


Now what i really need from you guys is a query which takes a Date and user ID and returns the events for that particular date!

Ok its seems quite difficult but i'm quite sure that you can come up with something simpler than my idea!!

I know i've posted the same question on another forum but no one answered so i'm hoping it would be more successful here! thanks and sorry!

Tahnks
Nick

 

[r937]

i'll bet everybody's stumped on the yynnnnn = sun/mon

i know i am

any calendar apps that i've worked with have taken the recurring events and actually pre-calculated the upcoming dates and loaded the event calendar with those recurrences

in other words, you do the complex date calculations on insert, not on select

rudy

http://r937.com/

SQL Consulting

 

[Forri]

So you're saying i should do multiple inserts for every recurency? and if so what happens if the event has been changed?

yynnnnn : each char represents a day; if the recurence was weekly then the weekly receunce in this case would be on Sunday and Monday;

Would it be nnyynny then the recurence would have been on Tuesday, Wed and Sat


Thanks
Nick

 

[r937]

no, i'm saying that's what you should do, i'm saying that's what i would do

makes the SELECTs a lot easier

and if the event changes? then you track down all recurrences (by event_id), delete them, and re-populate

rudy

http://r937.com/

SQL Consulting

 

[Forri]

I totally agree with you but if on the other hand i am updating an event and another user is or viewing an the same one then the whole thing will crash if the other guy opens an invalid id!

 

[r937]

that's why you delete first, then insert



rudy

http://r937.com/

SQL Consulting

 

[Forri]

What i was saying was that if user A has a list of events in front of him each having a HREF with the event's ID! Then user B edits an event the whoel list of ID is changed! then user A clicks on one of the List and obviusly the event is not found! do you get what i mean!

Thanks
nick

 

[r937]

yes, but that is true in any scenario where you delete something by ID number, when there could be urls out there with that ID number in them

example scenario: delete an event itself


rudy

http://r937.com/

SQL Consulting