Webcalendar, creating a photographic diary online, using Linux and PHP.

8 February 2006

I've been searching for a way to catalogue my photographs for some time. I tried creating my own web based photo manager using perl, but whilst it handled creating previews fine. I was still not happy with the way that I was storing information on the photos, and in particular how to create index files to store with my archived photos.

I didn't want a proprietary solution as I've been burned before with file formats that are no longer supported.

I have therefore decided to instead use a web based calendar and just have calendar entries whenever I use the camera. I store all my photographs using a date based folder structure, so this makes it easy to find the photos.

I have chosen the PHP based Webcalendar which is available under the GPL, and which was easy to install on my linux webserver. The one problem with this application is still creating indexes for my archived photos when I store them on CD (or increasingly DVD). The export option only handles future dates, and doesn't allow me to select my 2002 entries, and it only provides icalendar and vcalendar file formats.

I've therefore put together a quick hack to export the data from the webcalendar mysql database into an XML file suitable for including on the disc. This is a very quick hack, but it basically creates an xml file with all the entries for a particular year. I haven't found the way to display xml files in a browser properly (just raw xml not xhtml or anything), but what you can do is to open the page choose view source and then save it with a .xml file extension.

The file does not include any user authentication so it's recommended that if you do use this that you protect the folder using .htaccess / .htpasswd files or similar.

The source code is below (using the installation defaults - which you may have changed):


<?php
echo ("<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n");
echo ("<calendar>\n");
/* Exports PHP calendar year */

/* Warning this is a quick hack - no security checking etc. 
I suggest you protect this using .htaccess or similar so that
only server administrators can access */

/* Variables */
//$db_type = "mysql";
$db_host = "localhost";
$db_dbname = "intranet";
$db_login = "webcalendar";
$db_password = "webcal01";

/* URL Parameters */
$year = $_GET[year];

// Connect to the database server
$db = @mysql_connect($db_host, $db_login, $db_password);
if (!$db) {die( '<p>Unable to connect to the database server.</p>' );}

// Select the video database    
if (! @mysql_select_db($db_dbname) ) 
{
die( '<p>Unable to open Video DB.</p>' );
}

$result = @mysql_query("SELECT cal_id, cal_date, cal_time, cal_duration, cal_name, cal_description FROM webcal_entry where YEAR(cal_date) = $year");
if (!$result) 
{
die('<p>Error performing query: ' . mysql_error() . '</p>');
}

while ( $row = mysql_fetch_array($result) )
{
echo ("<entry id=\"$row[0]\">\n");
echo ("<date>$row[1]</date>\n");
echo ("<time>$row[2]</time>\n");
echo ("<duration>$row[3]</duration>\n");
echo ("<title>$row[4]</title>\n");
echo ("<description>$row[5]</description>\n");
echo ("</entry>\n");
}

?>
</calendar>

You call this page with the option ?year=xxxx.
For example if you call the page export.php and you want last years entries you can open http://yourwebsite/export.php?year=2005

If anyone has any suggestions on how to get the xml file out without the broswer messing up the formatting (particularly when you choose save), please let me know. Until then I've got a quick hack that works.