Jump to content
  • Advertisement
Sign in to follow this  
Yari

[web] PHP finding reoccurring calendar events

This topic is 4792 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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
Advertisement
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
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
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
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!