What's the best approach?

[acslater323]

I have a database-driven calendar page that pulls out records sorted by date and time for a user-specified date range. Easy enough. The catch, however, is that in the back end, I've added a check box to flag an event as recurring, with frequencies of Weekly, Bi-Weekly, and Monthly. I'd like to expand those options, but let's just leave it at that for now.

Here's how I'm tackling the problem right now to show all standard (non-recurring) events inline with the recurring events, in some basic pseudo code..

-Query the DB for all non-recurring events in specified date range.
-Build a 3D array of all these events like so:

Array (
[DATE] =>
Array (
[event1] =>
Array(
[title]
[time]
[description]
)
[event2] =>
etc....
)
[DATE] =>
Array (

etc...

)

-Now query the DB for all RECURRING events in the given range and loop through them, getting their frequencies. For each one, loop through the above array to find all the spots for these recurring events to go. For weekly events, for instance, the loop just keeps adding "+1 week" to the UNIX timestamp and creates new indexes in my 3D array.

-Finally, loop through the 3D array and show the calendar

Now.. all of this is very cumbersome, I know. We don't really like to work with 3D arrays if we don't have to. So someone made the suggestion that I should just loop a query for each day and find out if there are any events scheduled for that day, recurring or not.

My thinking is, it's never good to do a lot of DB queries in one script. For instance, if the user has set a one-year view of the calendar, that's a minimum of 365 queries in one script. That can't be good. Or does it really make more sense? It would certainly tighten up the code and make things easier, but isn't that a huge resource hog?

Any thoughts on these two options would be appreciated. Also open to any new perspectives. Can't be that uncommon a problem!

[darksidepuffin]

Whoever suggested looping for each day is very, very confused. The -right- way to do it would be, depending on the display mode (all sql queries are examples only -- just to give you a general idea of requirements...some functions may or may not be the best ones for the purpose):

Single day:
Select all rows for that day


SELECT * FROM table WHERE date = '02-26-07'


Entire week:
-Select all entries within the date range for the week
-Order by the day for which they're intended


SELECT * FROM table WHERE WEEKOFYEAR(date) = WEAKOFYEAR(NOW()) ORDER BY date ASC


Entire month:
-Select entire month
-Order by date


SELECT * FROM table WHERE MONTH(date) = '2' ORDER BY date ASC



Entire year:
-Select entire year
-Order by date


SELECT * FROM table WHERE YEAR(date) = '2007' ORDER BY date ASC


That would get you a full list for each day, week, month, and year respectively. It's one query -- but potentially a large number of rows returned -- which is why a lot of calendars only list events once the days are clicked.

[acslater323]

Wow. Many thanks for the WEEKOFYEAR function. That's a huge help. I'm not sure I see, however, how this addresses the recurring events, which are my main concern right now. In other words, if an event starts on a Monday, and it's flagged as a weekly recurring event, I want my calendar to output that event on every Monday shown.

One way, of course, is to insert hundreds of rows when someone marks an event as recurring, but that's pretty cheesy.

So, you can see why I'm going with the approach of building an array. Because an event only gets one insertion into the calendar, you need to use PHP to figure out how to write it out many times at regular intervals.

[darksidepuffin]

Oh...sorry...I was kinda sidetracked by the "looping" question. For recurring events...you'd just have to add some combination stuff to the query...for example..if you have a field, reoccur, and the numbers 1,2,3 are weekly, bi-weekly, and monthly, respectively...I'd probably (until I actually thought about it -- see below) have a "last date shown" field...lets call it last_date.

For a weekly reoccuring event, we'd do something like:


SELECT * FROM table WHERE ( WEEKOFYEAR(date) = WEAKOFYEAR(NOW()) AND reoccur = 0) OR (WEEKOFYEAR(date) = WEEKOFYEAR(DATE_ADD(last_date,INTERVAL 1 WEEKS)) AND reoccur = 1) OR (WEEKOFYEAR(date) = WEEKOFYEAR(DATE_ADD(last_date,INTERVAL 2 WEEKS) AND reoccur = 2) OR (WEEKOFYEAR(date) = WEEKOFYEAR(DATE_ADD(last_date,INTERVAL 1 MONTHS)) AND reoccur = 3)


And then while your looping, just collect the reoccuring dates, and update their last_date field.

Although....a better way might be to store a next_date, rather than a last date. That would simplify the query to:


SELECT * FROM table WHERE (WEEKOFYEAR(date) = WEEKOFYEAR(NOW()) AND reoccur = 0) OR (WEEKOFYEAR(date) = WEEKOFYEAR(next_date) AND reoccur > 0)


and then you could run a cron script to do update queries based on the values of reoccur, eg:


UPDATE table SET next_date = DATE_ADD(next_date,INTERVAL 1 WEEKS) WHERE DATE_DIFF(DATE(NOW()),DATE(next_date)) = 1 AND reoccur = 1

UPDATE table SET next_date = DATE_ADD(next_date,INTERVAL 2 WEEKS) WHERE DATE_DIFF(DATE(NOW()),DATE(next_date)) = 1 AND reoccur = 2

UPDATE table SET next_date = DATE_ADD(next_date,INTERVAL 1 MONTHS) WHERE DATE_DIFF(DATE(NOW()),DATE(next_date)) = 1 AND reoccur = 3

[acslater323]

Perfect. Thanks a lot for that. Didn't know you could do it all with SQL!

[darksidepuffin]

I'm glad I could help...to be honest I helped myself as much as I helped you -- I had to look into how I'd go about doing that, so I ended up learning how =)

[duff750]

I'm looking for something similar to this to add an events date page on a site I've been building. Would you mind sharing your setup by any chance?