Singular/Recurring Schedules in my CMS

Question

I'm building a CMS that has a requirement to allow users to store schedules. The interface is similar to MS Outlook, so the recurring schedules pretty much needs to mimic what that UI can support.

When the schedule is saved, we need to somehow be alerted when an event is supposed to happen. Right now, the plan is to have a cron job run every minute to check and see if an event is scheduled at that very minute.

Since our users will be from all over the world, we need this scheduling system to be time zone and DST aware.

Also, the ability to convert this schedule into human readable text (e.g. "Every day at 9am from Jan 1, 2010 to Feb 1, 2010") would be nice for our users.

Does anyone have any ideas how to implement this? I'm using PHP/MySQL.

Answer 1

Look at the iCalendar specification for ideas on what can be specified...

Answer 2

Your question looks more like a post to a job board, asking someone to just write your app for you.

It really isn't that complicated, what you're trying to do. Just set up a MySQL table to store appts, including maybe a name, description, beginning timestamp, ending timestamp and a "repeating" boolean. use Ajax to query the database, on whatever time interval you like to see if there are any approaching appointments. if true, notify the user.

for the time formatting, you could do something like this:

function format_date($timestamp1, $timestamp2,$repeating) {
    $str = "";
    if($repeating) {
        $str .= "Every Day";
    }
    $str .= "From ".date("g:i a",$timestamp1)." To ".date("g:i a",$timestamp2);
    return ($str);
}

Here are some queries you can look at and some table creation SQL:

CREATE TABLE `appointments` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) DEFAULT NULL,
    `description` text,
    `recurring` int(11) DEFAULT NULL,
    `recurrence_type` varchar(50) DEFAULT NULL,
    `starting_timestamp` int(14) DEFAULT NULL,
    `ending_timestamp` int(14) DEFAULT NULL,
    `end_recurring_timestamp` int(14) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

mysql_query("SELECT * FROM appointments 
WHERE recurring=0 
AND starting_timestamp <= ". time() + 86400 ." 
AND starting_timestamp > ". time() ." 
ORDER BY starting_timestamp DESC");

mysql_query("SELECT * FROM appointments 
WHERE recurring=1 
AND recurrence_type='DAILY' 
AND starting_timestamp <= ". time() + 86400 ." 
AND end_recurring_timestamp > ". time() ." 
ORDER BY starting_timestamp DESC");

The weekly and monthly recurrences will be more complex and will probably require manipulation outside of the SQL queries. Which may mean higher overhead.

For this example, i have store the dates as timestamps. however, it may be more reasonable to store them as a string and using something like strtotime(). although, i don't know how reliable that function is.

you can get the idea. As for timezone support, check out http://www.ultramegatech.com/blog/2009/04/working-with-time-zones-in-php/ and http://www.php.net/manual/en/class.datetimezone.php