Create downloadable iCal files in PHP/MySQL/Bootstrap?

April 30, 2013 1:57 PM

I have a PHP webpage that lists events happening at a conference by reading from a MySQL database. Is there an easy way to have PHP create downloadable iCal files so people can download individual events to their iphones/Androids etc., without my having to create individual event files, and without setting up a bunch of Google Calendar items to link to? (more detailed explanation inside.)

Website is for a four day conference, several individual event listings per day. It's only a yearly thing, so for that and other reasons we've decided that a full blown CMS system with a calendaring function is total overkill. I'm redesigning the site using Twitter Bootstrap, mainly to modernize the layout and CSS type stuff, but I'll be keeping it in PHP so I can maintain a few server includes I use that help me change over certain things year to year more easily.

For years I've just had the events page read from an "events" table in MySQL, that has basic columns - event ID#, date, start time, end time, name, location, description. The PHP formats that into tables to display on the webpage.

What I would love, would be for each event listing to have an iCal download link. But I'd like to not have to somehow manually create the iCal files. I was hoping there might be some way PHP could sort of write the iCal files on the fly or something like that?

I suppose I could enter them into a Google Calendar, and paste the individual links into the database for inclusion on the website. But again that would create a lot of manual work to enter all these events. The folks who run the conference just keep the event listings in Word, frankly. I end up reformatting to Excel/CSV for importing to the database. So if there were some PHP magic that could be done, it would be able so save me work each year.

I'm not very programming/scripting savvy, but some searching led me to believe this might be possible. I thought I might do better asking here where I could maybe get someone to talk me through it a bit while being patient with my pretty basic level PHP skills.

posted by dnash to Computers & Internet (16 answers total)

Writing iCal files on the fly is very doable. The file format is quite straight-forward and includes only plain text. The trickiest part is handling time zones (at least it was for me).

This Stack Overflow answer might get you started. I write Ruby more than PHP, so I don't have a lot of specific advice, but may be able to help anyway if you get stuck.
posted by duien at 2:06 PM on April 30, 2013 [1 favorite]

I have done something similar, though I used Perl to create the ics files. As noted above this is pretty easy given the text based, human readable format. Pretty much all the info you need is on the Wikipedia iClaendar page. The only other wrinkle, apart from aforementioned timezone issues, that comes to mind is correctly handling event updates if you will ever need to change anything once it is published.
posted by Quinbus Flestrin at 2:25 PM on April 30, 2013

Response by poster: Ok, the Stack Overflow link is helpful but - forgive my rudimentary knowledge - I'm still not quite clear on quite how to fit it into the framework I have. I have a concept of the iCal format, and a vague idea how I can use some of the PHP I already have to format the database fields into that format. What I'm missing is how to set up the file creation code surrounding some link text (or image - I'm imagining the little standard iCal button).

Here, I created a Pastebin of what I have right now and I wrote "CODE TO CREATE ICAL DOWNLOAD GOES HERE" where I think it should go.

Any pointers would be appreciated!
posted by dnash at 4:18 PM on April 30, 2013

You might want to have a look here.

Set your headers before outputting anything, then output your correctly formatted content.
posted by juv3nal at 4:27 PM on April 30, 2013

oh jeez I'm a dumb. that's the same link duien mentioned.

See the code in that stack overflow answer? paste all that in a separate file and call it "make_my_ical_file.php" or something. Then from your page just have a "<a href="make_my_ical_file.php">click here to get an ical file </a>"

You'll still need to make changes to make_my_ical_file.php to make it output the correct stuff of course
posted by juv3nal at 8:27 PM on April 30, 2013

Response by poster: juv3nal - thanks, I was just starting to grasp that that was the way things should work. What I don't grasp is how do the correct values get passed to the "make_my_ical_file.php" file?

So, the webpage ends up with this table, like,

May 25 - 2:00pm to 3:00pm - Event Name - Location - Description - iCal Link

How is it that clicking the "iCal Link" to this separate PHP file fills that file with the times, event name, location, and description from THAT line of the table/database, and not another?

Or, said another way, if I write this "make_my_ical_file.php" file so that it sets up the ical format and has something like "DTSTART:" where does that piece of code fill in the variable from?
posted by dnash at 8:52 PM on April 30, 2013

Best answer: The quick and dirty way is to probably tack whatever variable you need onto the end of the link e.g."make_my_ical_file.php?myvar=myvarvalue" in which case it's a GET variable which is accessed in php by $_GET['myvar'] and you can do whatever queries/logic etc. you need to with it inside make_my_ical_file.php

You can do multiple variables by "make_my_ical_file.php?myfirstvar=myfirstvarvalue&mysecondvar=mysecondvarvalue" etc.

Another way is to scrap using <a> and make an html form.
Then if you give the form tag the attributes action="make_my_ical_file.php" and method="post" or method="get" the various form elements will map to $_POST['input_element_name'] and $_GET['input_element_name'] respectively if you hit submit.

Be aware that there are security implications if you don't properly validate the contents of those variables before using them. In general, if you are doing database-y stuff, you should look into using prepared statements.
posted by juv3nal at 9:45 PM on April 30, 2013

Best answer: So here is an example of one possible make_my_ical_file.php file.

https://gist.github.com/artlung/5493859 is the link to the code.

Should be fairly self-explanatory. This presumes that your database uses the column name "id" and that's a numeric value. If there's a different primary key for the events let me know and that can be adjusted.

You should be able to plop that .php file on your server and get *something* explanatory. Set $use_database to true to actually connect to your database.

Also, you can use this ical validator to validate the url or the .ics file that is downloaded to see if it's formatted right.

Now, one unknown is how ical handles timezones - whether that needs to be a UTC date or whether you can set the timezone as part of the ical itself. This stackoverflow question may be helpful there.
posted by artlung at 10:24 PM on April 30, 2013

Don't get too hung up on making the calendar files on the fly.... It's pretty inefficient to keep recreating them when it sounds like you're only adding/changing events infrequently (once a day or less?). Just write a script to make all the calendars once (offline on your own computer, even) and re-run as necessary when you add new events. Make the file names database IDs and it will be simple to link them where you need them.
posted by anaelith at 3:49 AM on May 1, 2013

Response by poster: Artlung - thanks, that's fantastic. I gave your example a quick test run this morning using just the dummy data (no database connection) and to text file, and it appeared to work.

A question though about how the "make_my_ical_file" is supposed to connect to the database? In the sample code I showed from my site, I left out that there's an include file that contains database path, username and password to connect to MySQL. Would that info need to be in the "make_my_ical_file" code also? (my assumption is yes...) If so, as an include? Or put the actual contents of the include file right in it?
posted by dnash at 9:09 AM on May 1, 2013

Yes, you'd need to include your db connection info somehow.
For security reasons it's best to:
a) include it as a separate file
b) have that file reside outside your web root
posted by juv3nal at 11:55 AM on May 1, 2013

Correct, that's a PHP file that should connect to the database in whatever way makes sense for your application. Likely as an include file that does the mysql_connect or provides the variables you need to make a successful mysql_connect.
posted by artlung at 9:18 PM on May 1, 2013

Response by poster: So, it's totally working, but I have to figure out the timezone thing, because the events are importing to my calendar five hours off.

artlung, based on the link you gave to timezone help, I think this is giving the times in UTC, with a "Z" on the end

$icalstarttime   = date(DATE_ICAL, strtotime( $row['eventStart']))

result is "DTSTART:20130524T210000Z"

I tested manually editing the results to remove the "Z" and it corrected the time when imported to my calendar app. However, I realize that works for me cuz I'm already in the right timezone.

I think, based on some googling, it would work if I can lose the "Z" and add a "TZID"

DTSTART;TZID=America/Chicago:20130524T210000
posted by dnash at 5:56 AM on May 2, 2013

Yes, that's basically what the StackOverflow question I linked to was saying.

I updated the gist and here's a list of valid timezone values. I changed the DATE_ICAL value (this drops the "Z" off) and made the changes in the body to include it. I think that gets you where you want to be.
posted by artlung at 6:58 AM on May 2, 2013

Response by poster: The DATE_ICAL value looks the same to me?

I should've maybe mentioned that I had already altered it from what you originally had, because I found I needed to change the data type of the "eventStart" and "eventEnd" fields in the database. They were just times before, but I changed them to DATETIME so each has YYYY-MM-DD HH:MM:SS. Reason is some events are nighttime parties that go past midnight. When displaying on a webpage, "5/25/2013 9:00 pm - 1:00 AM" looks fine, but a calendar app would choke because it appears to end before it starts. So I realized I needed to add dates to the end times.

What I'm getting at is the $icalstarttime and $icalendtime don't need to refer to the "eventdate" anymore, the date is included in eventStart and eventEnd.

I emailed a file to a friend in a different timezone this morning to test what the time does when he imports it.

Wait - (edit) I think I see what you did - define("DATE_ICAL", "Ymd\THis"); I hadn't noticed that line before.
posted by dnash at 8:24 AM on May 2, 2013

Right, you can see the edits I made here in this view of the gist, "dropping the Z" is necessary because the "Z" implies UTC, and with a text timezone specifier (America/Chicago) that has to go away.

I really do recommend checking out the ical validator. Helped me see what I was doing wrong.

(aside: In general the ical format appears very ugly to my eye. it's new to me).
posted by artlung at 10:56 AM on May 2, 2013