Events Calendar with Repeating Events.

[chronister]

I did the tutorial here[url=http://www.phpfreaks.com/tutorials/83/0.php]http://www.phpfreaks.com/tutorials/83/0.php[/url] and understand most of it. I modified it to use mysql and get events. It works. I have a form to add events and it works. I racked my brain on how to come up with repeating events and I finally did. This is where I run into the problem. I am sending about 120-140 queries to the database each time I display the calendar.

[b]Events Table fields is set up as follows:[/b]

[table]
[tr]
[td]Field[/td]
[td]Type[/td]
[td]Null[/td]
[td]Key[/td]
[td]Default[/td]
[td]Extra[/td]
[/tr]     
    [tr]
    [td]id[/td]
    [td]int(11)[/td]
    [td]NO[/td]
    [td]PRI[/td]
    [td]NULL[/td]
    [td]auto_increment[/td]
[/tr]
[tr]
    [td]start_date[/td]
    [td]int(11)[/td]
    [td]YES[/td]
    [td][/td]
    [td][i]NULL[/i][/td]
    [td][/td]
[/tr]
    [tr]
    [td]title[/td]
    [td]varchar(255)[/td]
    [td]NO[/td]
    [td][/td]
    [td][/td]
    [td][/td]
[/tr]
    [tr]
    [td]description[/td]
    [td]varchar(255)[/td]
    [td]NO[/td]
    [td][/td]
    [td][/td]
    [td][/td]
[/tr]
    [tr]
    [td]end_date[/td]
    [td]varchar(50)[/td]
    [td]YES[/td]
    [td][/td]
    [td]NULL[/td]
    [td][/td]
[/tr]
    [tr]
    [td]repeating[/td]
    [td]varchar(50)[/td]
    [td]YES[/td]
    [td][/td]
    [td]NULL[/td]
    [td][/td]
[/tr]
[/table]

Events are stored with a Unix timestamp. The repeating field is daily, monthly, yearly, or weekly.

You can view the calendar here: [url=http://71.38.22.36/calendar.php]http://71.38.22.36/calendar.php[/url]

You can view a dynamic version of the source here: [url=http://71.38.22.36/view-code.php]http://71.38.22.36/view-code.php[/url]

I know that 150-160 queries per page are too much and I need to cut them down. Can someone help me find a better solution to this. I spent many hours thinking about how to determine whether or not an event repeats without having to put an entry for every single one.

[url=http://71.38.22.36/view-code.php?id=2]http://71.38.22.36/view-code.php?id=2[/url] has the functions that run once for each day that the calendar paints. It is 4 seperate queries to grab events that repeat on a certain basis (daily, monthly, weekly etc). calendar.php runs these 4 queries in a foreach loop that builds the days for the calendar.

You can see the number of database calls in the lower left corner of calendar.php & add_event.php. Can anyone help me reduce these?

Thanks in advance,

Nathan

[chronister]

*bump*

[dgiberson]

pull all the records required in one query to and put them into a multidimensional array, then loop through the array as necessary...

[chronister]

I thought about doing that, but I am not sure how to narrow down to the events that I want. I don't want to get all events in the database, and then weed through them to determine what event may fall on a particular day.

As it loops through 2 foreach statements and creates calendar table it also takes the day it is working on and runs a query against it. This is the only way I could see to get the events that today could be part of.

By doing this I automatically have at least 28 - 31 queries there. I have a start date, end date and repeat interval. The other way I thought about is simply putting one entry for each time the event repeats. So if I repeat an event for 5 days, it adds 5 rows to the database. This seems very inefficient so I decided to go with as little as necessary. 3 fields.

I am simply lost as to how to optimize this and would like some guidance in the right direction.

The whole thing is working with the exception of a couple minor annoyances... should I even be concerned with  over a hundred queries per page load or am I right in thinking that this is a ridiculous number of queries?

Thanks,

Nate

[obsidian]

Well there are a couple ways to logically reduce your number of queries. The easiest is simply find a way to [b]only[/b] query for events on the days where you already know there are events occurring. Basically, what you want to do is run one query when the page loads to get an array of days this month that contain events. Then, you can limit your queries to each day that you already know contains events. This may only strip a few of your actual queries, but for those months where you have only a few days with events, it can drastically reduce your query count.

[chronister]

That is a good Idea, but I am not sure how it would work for me. The way I have my db set up is the event has an entry like so...

[b]
Event Title        Event Description            Start Date                End Date          Repeat Frequency.
[/b]

Early School          Kids out 1 hr early        8-31-06                        7-09-07            Weekly
Dismissal     


So if I query the db for events that fall on current month, this event would not show up because it did not start in this month.

The calendar is painted on screen one day at a time using a couple foreach loops. my query runs in this loop.  The $t variable is the day it currently working on in the loop.

[code]SELECT * FROM events WHERE start_date <= $t && end_date >= $t && repeating=weekly[/code]

This would select all events that began today or before and end today or after, this tells me that today could have an event, so after I get the events I compare the event to a formatted date to tell me if the event falls on today.

This is the only way I can see to do it right now. I may need to change how the db stores stuff, but this is the best way I can see to do it, and I have a ridiculous amount of queries. I also found another problem that I will have to work out on my own in regards to how I determine if $t has an event that falls on it.

@obsidian  - Thanks for the response. I appreciate it.

[obsidian]

Ah, yes, I see where you're coming from. The best way I can think of to remove your repetitive queries, then, would be to pull [b]all[/b] events who's range surrounds the current date, and then use PHP to calculate whether or not it should be displayed on that date based on your repeat frequency. So, something like this may help. The key to this working properly is in the assumption that your date fields are stored as MySQL DATE or DATETIME fields (YYYY-MM-DD format):

[code]
<?php
// within your loop for each day of the month
// assuming $date holds the YYYY-MM-DD value of the current day of the month in your loop
$sql = mysql_query("SELECT * FROM events WHERE '$date' BETWEEN start_date AND end_date");
if (mysql_num_rows($sql) > 0) {
  // We have at least one event, let's parse it to see if it is to be dislpayed
  while ($event = mysql_fetch_array($sql)) {
    $start = $event['start_date'];
    $end  = $event['end_date'];
    $freq  = $event['frequency'];
    switch($freq) {
      case 'daily':
        // It's happening daily, so show it.
        break;

      case 'weekly':
        // figure out if the date is the same day of the week as start date
        if (date('w', strtotime($start)) == date('w', strtotime($date))) {
          // Same day of the week, so display it
        }     
        break;
    }
  }
}
?>
[/code]

Hope this helps. If you can get something like this to work for you, you'll be able to narrow your queries down to one per day. Plus, with a switch statement like that, you could come up with algorithms to figure in daily, weekly, bi-weekly, monthly, etc.

Good luck!

[chronister]

Geez, over 3700 hits to these pages and no one else has any input??

And to the person at who deleted all events, your simply rude, but you missed a few way back in 2005  LOL.

I was going to address you by your IP address, but don't know if the moderators would appreciate it.

Thanks to those who answered and tried to help.

The pages are no longer accessible to the public anymore.

@obsidian, Thanks I will try to work on something like that.