Hi I have been reading lots of recurring topics but still zero ideas on how to implement this logic to my calendar application. I have this table:
id | title | start | end | recurring
1 | test1 | 2011-07-25 08:30:00 | 2011-07-25 10:30:00 | day
2 | test2 | 2011-07-26 08:30:00 | 2011-07-26 10:30:00 | week
3 | test3 | 2011-07-26 08:30:00 | 2011-07-26 10:30:00 | month
4 | test4 | 2011-07-26 08:30:00 | 2011-07-26 10:30:00 | year
When a user view the calendar, I have the parameters send to a url to fetch the data from the database. For example:
Month view: http://www.domain.com?start=2011-07-01&end=2011-07-31
Week view: http://www.domain.com?start=2011-07-25&end=2011-07-29
What I have is this so far:
SELECT * CASE recurring
THEN IF(DATE_FORMAT(CURDATE(),"%j") = DATE_FORMAT(e.start,"%j"),1,0)
THEN IF(DATE_FORMAT(CURDATE(),"%w") = DATE_FORMAT(e.start,"%w"),1,0)
It can retrieve only once...but if I view the calendar for next week 2011-08-01 to 2011-08-05, it will not work.
My question is, how do I retrieve the recurring events based on the url parameters? Any help and advise is greatly appreciated.
PS: I did stumble across this thread: http://stackoverflow.com/questions/1751380/php-5-3-datetime-for-recurring-events and it seems close to what I needed but just that I can't figure out in my case how to implement it dynamically with my start and end date time.
I found this function http://www.sitepoint.com/forums/php-34/recurring-events-668009.html#post4553013 that does this (i suppose?) in PHP code level but does it means that I have to retrieve the whole database record and iterate thru all the records with that function?