[web] PHP finding reoccurring calendar events
Author
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.
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
Just make one big SQL statement, unless you really need to get each seperately
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.
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.
Author
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.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
