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
WHEN "day"
THEN IF(DATE_FORMAT(CURDATE(),"%j") = DATE_FORMAT(e.start,"%j"),1,0)
WHEN "week"
THEN IF(DATE_FORMAT(CURDATE(),"%w") = DATE_FORMAT(e.start,"%w"),1,0)
END
FROM event;
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.