PHP, MySQL, iCal, ICS

Posted on by Gowtham

Disclaimer

These instructions/steps worked for me in CentOS and MacBook Pro. It may very well work for you on Red Hat-like or other and Macintosh distributions. Please note that if you decide to use these instructions on your machine, you are doing so entirely at your very own discretion and that neither this site, sgowtham.com, nor its author is responsible for any/all damage – intellectual or otherwise.


For whatever reason, I keep track of my schedule via a PHP-MySQL based web utility. But I also use Apple Macintosh computers (PowerBook G4 and MacBook Pro) quite extensively for a variety of purposes and would love to keep my powerful little iPhone synchronized as well. I have been working it for a while and I think I finally have got it to work. The procedure follows, as usual, in detail:

Apache, PHP, MySQL

It is my practice that I do a full/maximal installation of any linux distribution and that takes care of installing Apache (with all the required modules), PHP, MySQL, etc. I bet there are tons of documents online that you can refer and install them if you don’t already have them.

Storing Events In MySQL Database

Assuming that we would be working with a database called Library, the structure of the MySQL table to store the event details is as follows:

1
2
3
4
5
6
7
8
9
10
11
12

CREATE TABLE `Library`.`Schedule` (
  `ID`          INT(11) NOT NULL AUTO_INCREMENT,
  `StartDate`   DATE NOT NULL DEFAULT '0000-00-00',
  `StartTime`   TIME NOT NULL DEFAULT '00:00:00',
  `EndDate`     DATE NOT NULL DEFAULT '0000-00-00',
  `EndTime`     TIME NOT NULL DEFAULT '00:00:00', 
  `Category`    VARCHAR(255) DEFAULT NULL,
  `Name`        VARCHAR(254) NOT NULL DEFAULT '',
  `Location`    VARCHAR(254) DEFAULT NULL,
  `Description` text DEFAULT NULL,
  UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET utf8 COLLATE utf8_unicode_ci AUTO_INCREMENT=1 ;


A few events, for example, would look like:

1
2
3
4
5

INSERT INTO `Schedule` VALUES ('', '2008-05-26', '20:30:00', '2008-05-26', '23:00:00', 'SPORTS', 'NBA', 'Red Bank, NJ', 'Eastern Conference Finals Game #4
Celtics @ Pistons');
INSERT INTO `Schedule` VALUES ('', '2008-05-27', '18:00:00', '2008-05-27', '23:59:00', 'PERSONAL', 'Showcase', 'Red Bank, NJ', 'Coding of a new photoblog software.');
INSERT INTO `Schedule` VALUES ('', '2008-05-27', '21:00:00', '2008-05-27', '23:30:00', 'SPORTS', 'NBA', 'Red Bank, NJ', 'Western Conference Finals Game #4
Lakers @ Spurs');

Generating ICS File

Now that the events are stored in a database, the following PHP script will extract them and write them in ICS format, so that iCal can subscribe to it. First part is to connect to the database.

1

<!--?php # Connect to the database $host = "localhost"; $dbuser = "MYQL_USER"; $dbpasswd = "MYSQL_PASSWD"; $database = "Library"; $connect = mysql_connect($host, $dbuser, $dbpasswd) or die(mysql_error()); mysql_select_db($database,$connect) or die(mysql_error()); ?-->

Next part is to query the database and get the results:

1

<!--?php # Query the database and get the results $sql = "SELECT * FROM `Library`.`Schedule` "; $result = mysql_query($sql); $nresult = mysql_num_rows($result); ?-->

Next thing is to write the header part of the ICS file.

1

<!--?php $ics_contents = "BEGIN:VCALENDAR\n"; $ics_contents .= "VERSION:2.0\n"; $ics_contents .= "PRODID:PHP\n"; $ics_contents .= "METHOD:PUBLISH\n"; $ics_contents .= "X-WR-CALNAME:Schedule\n"; # Change the timezone as well daylight settings if need be $ics_contents .= "X-WR-TIMEZONE:America/New_York\n"; $ics_contents .= "BEGIN:VTIMEZONE\n"; $ics_contents .= "TZID:America/New_York\n"; $ics_contents .= "BEGIN:DAYLIGHT\n"; $ics_contents .= "TZOFFSETFROM:-0500\n"; $ics_contents .= "TZOFFSETTO:-0400\n"; $ics_contents .= "DTSTART:20070311T020000\n"; $ics_contents .= "RRULE:FREQ=YEARLY;BYMONTH=3;BYDAY=2SU\n"; $ics_contents .= "TZNAME:EDT\n"; $ics_contents .= "END:DAYLIGHT\n"; $ics_contents .= "BEGIN:STANDARD\n"; $ics_contents .= "TZOFFSETFROM:-0400\n"; $ics_contents .= "TZOFFSETTO:-0500\n"; $ics_contents .= "DTSTART:20071104T020000\n"; $ics_contents .= "RRULE:FREQ=YEARLY;BYMONTH=11;BYDAY=1SU\n"; $ics_contents .= "TZNAME:EST\n"; $ics_contents .= "END:STANDARD\n"; $ics_contents .= "END:VTIMEZONE\n"; ?-->

Next, loop through the results and write all the event details in ICS format, one at a time:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

<!--?php 
while ($schedule_details = mysql_fetch_assoc($result)) {
  $id            = $schedule_details['ID'];
  $start_date    = $schedule_details['StartDate'];
  $start_time    = $schedule_details['StartTime'];
  $end_date      = $schedule_details['EndDate'];
  $end_time      = $schedule_details['EndTime'];
  $category      = $schedule_details['Category'];
  $name          = $schedule_details['Name'];
  $location      = $schedule_details['Location'];
  $description   = $schedule_details['Description'];
 
  # Remove '-' in $start_date and $end_date
  $estart_date   = str_replace("-", "", $start_date);
  $eend_date     = str_replace("-", "", $end_date);
 
  # Remove ':' in $start_time and $end_time
  $estart_time   = str_replace(":", "", $start_time);
  $eend_time     = str_replace(":", "", $end_time);
 
  # Replace some HTML tags
  $name          = str_replace("&lt;br&gt;", "\\n",   $name);
  $name          = str_replace("&amp;amp;", "&amp;",    $name);
  $name          = str_replace("&amp;rarr;", "--&gt;", $name);
  $name          = str_replace("&amp;larr;", "&lt;--", $name);
  $name          = str_replace(",", "\\,",      $name);
  $name          = str_replace(";", "\\;",      $name);
 
  $location      = str_replace("&lt;br&gt;", "\\n",   $location);
  $location      = str_replace("&amp;amp;", "&amp;",    $location);
  $location      = str_replace("&amp;rarr;", "--&gt;", $location);
  $location      = str_replace("&amp;larr;", "&lt;--", $location);
  $location      = str_replace(",", "\\,",      $location);
  $location      = str_replace(";", "\\;",      $location);
 
  $description   = str_replace("&lt;br&gt;", "\\n",   $description);
  $description   = str_replace("&amp;amp;", "&amp;",    $description);
  $description   = str_replace("&amp;rarr;", "--&gt;", $description);
  $description   = str_replace("&amp;larr;", "&lt;--", $description);
  $description   = str_replace("&lt;em&gt;", "",      $description);
  $description   = str_replace("&lt;/em&gt;", "",     $description);
 
  # Change TZID if need be
  $ics_contents .= "BEGIN:VEVENT\n";
  $ics_contents .= "DTSTART;TZID=America/New_York"     . $estart_date . "T". $estart_time . "\n";
  $ics_contents .= "DTEND:"       . $eend_date . "T". $eend_time . "\n";
  $ics_contents .= "DTSTAMP:"     . date('Ymd') . "T". date('His') . "Z\n";
  $ics_contents .= "LOCATION:"    . $location . "\n";
  $ics_contents .= "DESCRIPTION:" . $description . "\n";
  $ics_contents .= "SUMMARY:"     . $name . "\n";
  $ics_contents .= "UID:"         . $id . "\n";
  $ics_contents .= "SEQUENCE:0\n";
  $ics_contents .= "END:VEVENT\n";
}
?-->

Next step is to write the tail part of the ICS file and write whole stuff into a flat text file (schedule.ics):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

<!--?php 
$ics_contents .= "END:VCALENDAR\n";
 
# File to write the contents
$ics_file   = 'schedule.ics';
 
if (is_writable($ics_file)) {
  if (!$handle = fopen($ics_file, 'w')) {
     echo "Cannot open file ($ics_file)\n\n";
     exit;
  }
 
  # Write $ics_contents to opened file
  if (fwrite($handle, $ics_contents) === FALSE) {
    echo "Cannot write to file ($ics_file)\n\n";
    exit;
  }
 
  # echo "Success, wrote to &lt;b&gt;schedule.ics&lt;/b&gt;&lt;br&gt;\n\n";

  fclose($handle);
 
} else {
  echo "The file &lt;b&gt;$ics_file&lt;/b&gt; is not writable\n\n";
}
?-->

Auto-Generating ICS File

Next thing to worry about is to generating this schedule.ics after adding new events to the MySQL database. Assuming that the events were stored in a flat file called schedule.sql and the above mentioned PHP script was saved as schedule_ics.php, the following bash script may be used to that effect:

1
2
3
4
5
6
7
8

#! /bin/bash
 
export db_user='MYSQL_USER'
export db_pass='MYSQL_PASSWD'
 
mysql -u $db_user -p$db_pass -h localhost &lt; schedule.sql
 
php schedule_ics.php

Subscribing Via iCal

Assuming that the above generated ICS file is located at http://your-domain.com/schedule.ics, following screenshots may be used to subscribe via iCal:


iCal
iCal
iCal

A Working Example

I followed these steps to generate the ICS file of my schedule. You can check it out if you wish.

7 Replies to “PHP, MySQL, iCal, ICS”

  2. Awesome. Finally find just what I needed. Thanks so much for posting this code. It is just what I needed for getting schedule into a form that some dumb users can use. Worked perfectly!

    Reply

  4. This was a great start for me, but I would add that it’s not necessary to generate a physical ics file with a cron job. You can simply echo the value of $ics_contents, then just subscribe to schedule.php setting iCal to update at a regular interval. It will treat the php file as though it’s an ics.

    Reply

  5. Great tutorial. There was one little thing I couldn’t figure out on my own with ics files for different events overwriting each other.. Make sure that the UID is different for each event ics. Having different names doesn’t do the job.

    Reply

  6. Thanks for a great script! Saved me countless hours.

    I use it to generate an ics for an individual event as a download – for my needs I had to remove the following line or multiple calendars were being created:
    $ics_contents .= “X-WR-CALNAME:Schedule\n”;

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.