Recurring Events

[jaslowry]

I have searched the forums, as well as online, and have not found anything on how to do this. I have a calendar that I'm currently building, and need to be able to have recurring events. How can I go about doing this?

Currently the code that I'm using to pull the events from the database is:

function checkEvents($month, $onday, $year) {
if($onday <= 9){
$oday = "0$onday";
}else{
$oday = $onday;
}
$ectQuery = mysql_query("SELECT * FROM 'thedatabase` WHERE date = '$month/$oday/$year' order by date asc LIMIT 1") or die(mysql_error());
$ectResult = mysql_fetch_array($ectQuery);
if($ectResult != ''){
echo "\t\t\t\t<div class=\"days\" onMouseOver=\"this.style.cursor='pointer',this.style.backgroundColor='#74879e'\" onMouseOut=\"this.style.backgroundColor='#617185'\" onclick=\"window.parent.location='/calendar/events.php?id=".$ectResult{'id'}."'\"><b>{$onday}</b><div class=\"event\">".$ectResult{'small_desc'}."</div></div>\n";
}else{
echo "\t\t\t\t<div class=\"days\"><b>{$onday}</b></div>\n";
}
}

[dreamcatcher]

Hi jaslowry,

Is this something you are building for yourself or other visitors? You could set up a cron job/tab to execute recurring events and auto add the dates into your database?

dc

[jaslowry]

This is something for myself. how would I go about doing it with a cronjob?

[idfer]

You can also do this with SQL, depending on how the events recur (note i haven't tested any of the below):

Let's say the event recurs once a week:

SELECT * FROM thedatabase WHERE date = '$month/$oday/$year' OR (recur_mode = 'weekly' AND WEEKDAY(date) = WEEKDAY('$month/$oday/$year')) order by date asc LIMIT 1

Once a year:

SELECT * FROM thedatabase WHERE date = '$month/$oday/$year' OR (recur_mode = 'annual' AND DAY(date) = $day AND MONTH(date) = $month) order by date asc LIMIT 1

Once every n days:

SELECT * FROM thedatabase WHERE date = '$month/$oday/$year' OR (recur_mode = 'ndays' AND DATEDIFF('$month/$oday/$year',date) % ndays = 0) order by date asc LIMIT 1

In the above, recur_mode and ndays would be extra fields in your table. Caveat: if you have a lot of recurring events, the query may do a full table scan. You can optimize that by storing the WEEKDAY(date) / DAY(date) / MONTH(date) parts of the event as separate fields in the table.

Hope this helps.