Sign in to follow this  
Yari

[web] PHP finding reoccurring calendar events

Recommended Posts

Yari    122
Hi. I have a MySQL database which stores events with the day, month, and year they occurr as well as the frequency with which they reoccurr. (Monthly/Yearly/Daily/etc.) I then have a form where users can search for events by the day, month, year they occurr etc., the form then makes a sql query selecting all the events that occurr originally at that timeframe. The database only holds the original date that the event occurred, so I want to add statements to the query to find the events that reoccurr in that timeframe. So far I'm using... user enters day, month, year. sql = (All events originally occurring); if !day { if !month { if !year { // nothing }else{ sql = sql."OR (frequency=yearly)" } }else{ if !year { sql = sql."OR (frequency=monthly)" }else{ sql = sql."OR (frequency=monthly) OR (frequency=yearly AND month='month')" } } }else... etc, etc... I wanted to know if this is the most efficient way to find every event I need, and if there are any feilds I could add to the database to make it easier. Also I'm lost on how to sort the results by date. Since they only have their original date in the database. I imagine I would have to add code to figure out the exact day an event would land and add that feild to an array. Although I'm sure exactly how to go about this. Also there's the possiblity of an event showing up multiple times in the search results. Thanks for any help/suggestions.

Share this post


Link to post
Share on other sites
Extrarius    1412
select ... where (Day=# AND Month=# AND Year=#) OR (Day=# AND Month=# AND Freq=Yearly) OR (Day=# AND Freq=Monthly) OR (Freq=Daily)

Just make one big SQL statement, unless you really need to get each seperately

Share this post


Link to post
Share on other sites
indieboy    133
on phpCalendar they have a separate table for repeats and then they calculate on php the events order.

Also i'am writing a newsletter engine, on which the user may schedule the email delivery:
- send one time only
- daily-basis
- weekly-basis (with an option to select several days on the same week)
- monthly-basis (with option to select a specific day or a relative day, eg. first monday of the month)

i calculate the next time a email has to be sent using php, not mysql. i don't know if there's a way to do that with queries.

Share this post


Link to post
Share on other sites
Yari    122
Quote:
Original post by Extrarius
select ... where (Day=# AND Month=# AND Year=#) OR (Day=# AND Month=# AND Freq=Yearly) OR (Day=# AND Freq=Monthly) OR (Freq=Daily)

Just make one big SQL statement, unless you really need to get each seperately


This seems to make sense, but what if the user only enters a month/year/etc?
You wouldn't find anything since the other feilds = '' right?
So you would need;

WHERE (Day=# AND Month=# AND Year=#)
OR (Month=# AND Year=#)
OR (Day=# AND Month=#) etc..
But that would work either, or am I completly off track here?

Anyway, the nested if's work for now, I decided just to display the reoccurring events in a different table, and sort them by date separatly.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this