Recurring Events with Mysql
I was wondering what the easiest way of being able to store events in a mysql database and then having a list on my main page which would show the 6 events that where coming up soon. So I trying to look for the php code that would make this possible.
Is there a certain mysql datatype I should use for the date field in order to make some kind of equality statement or what?
Thanks in advance.
Greg K posted this at 18:25 — 6th July 2005.
He has: 2,145 posts
Joined: Nov 2003
It would really depend on what type of recurring events you are having (repeat every week, every x days, every 3rd monday of the month, etc). Also, what would the likelyhood be of so few events that a recurring item may need to come up twice in the next 6 items.
As long as you know that the same event wouldn't show up twice, and you don't care about past due ones, the script would be fairly easy, have feilds like the following:
eventID (autonum int)
eventDate (date)
eventDesc (varchar)
eventRecNum (int)
eventRecType (char)
If it is recurring, you have values in the RecNum and RecType. The RecType would be a character to indicate type of repeating, like D for days, W for weeks, M for months, etc.
Examples:
eventRecNum = 30, eventRecType = 'D' : repeat every 30 days
eventRecNum = 1, eventRecType = 'M' : repeat every month
eventRecNum = 2, eventRecType = '1' : repeat every second monday (0=sunday, 1= monday, 2=tuesday, etc)
eventRecNum = -1 eventRecType = '4' : Repeat every last thrusday of the month.
You could program this however you want, this was just a scheme i came up with right now off the top of my head.
At the start of the script, do a DELETE FROM events WHERE (eventDate < now() AND eventRecNum IS NULL) This will delete any old dates that do not repeat. then do a loop of SELECT * FROM events WHERE eventDate < now() (there will only be recurring dates left) Loop though each item, and adjust the date based on when the even will recur. then update the record in the database with the new date.
Set this script to run via CRON job during the night.
Then on the script to get the dates, you can do SELECT * FROM EVENTS ORDER BY eventDate LIMIT 6. Test this though, not sure if the LIMIT 6 will work right. It may get the first 6 rows and then sort them, But i think it should order all rows then get the first 6. (never used this, so test it).
-Greg
RenardMF posted this at 18:36 — 6th July 2005.
They have: 13 posts
Joined: Mar 2005
Thanks for the quick response.
Ya sorry i should have been more specific. Im trying to display events that occur once a year like deadlines for contests. So the recurring part would be that the come around next year. And the 6 items thing is just a limit on the amount of syndication being done by showing which ones are in the near future out of the many entries I put in.
But what you gave me so far looks good. I will try to mess around with it.
Thanks again for your help.
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.