3

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: 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.

Community
  • 1
  • 1
pakito
  • 387
  • 2
  • 3
  • 17
  • 1
    Might be a good idea to show us any code you're currently using to fetch data from your database. – Tom Walters Jul 25 '11 at 13:19
  • What does the end date mean? That after that, the recurring date is not recurring anymore? How do you know when a recurring event is not recurring anymore? I don't think your current design can handle recurrence in events appropriately. – Jacob Jul 25 '11 at 13:20
  • I would edit your question to include that code so others see it straight away. – Tom Walters Jul 25 '11 at 13:30
  • @cularis: The end date is just simply the end date. An event can happen 1pm-3pm daily, weekly, or monthly. There is no field to indicate when the recurring will end. If a user view the calendar for the year of 3100, it will still display the event nonetheless. I believe this is normal for all calendars recurring logic. – pakito Jul 25 '11 at 13:31
  • Sorry tom, just updated my first post. – pakito Jul 25 '11 at 13:33
  • *(tip)* [Martin Fowler: Recurring Events for Calendars (pdf)](http://martinfowler.com/apsupp/recurring.pdf) – Gordon Jul 25 '11 at 13:36
  • Thanks Gordon, I have read that from Martin fowler before but to be honest, if I can understand that, I can be sure I'll be Martin Fowler number 2...lol. – pakito Jul 25 '11 at 13:37

1 Answers1

0

this is for week and if u change week to monthly etc.. it will work

$sql = "Select form tablename where start > '".$_GET['start']."' and end = '".$_GET['end']."' where recurring = 'week'"

user860572
  • 101
  • 1
  • 5
  • I believe you have misunderstood my question. Your sql statement only retrieve once for the given event date. What I want is to retrieve an event "weekly" if the recurring is set to "week". But thanks nonetheless. – pakito Jul 25 '11 at 13:35