Cycling Edinburgh

Technical aspects of this site

Notes on the software and programming techniques used to manage the event listings on this site

By Mike Lewis, December 2008
Updated June 2009

If you glance at this site's home page, you'll see a list of bike rides and other cycling-related events. At first glance, it looks like the sort of straightforward web page that you might create in an editor such as FrontPage or perhaps code by hand. In fact, there are some interesting technical aspects behind the site, which I'll describe in this article - in the hope they might be of passing interest to fellow software developers.

Although the site is concerned entirely with cycling, the tools and techniques that I'll describe could be applied to any type of event-listing website.

On the desktop

When I started the site in 2006, I created an initial home page in Microsoft FrontPage. Every two to three weeks, I would hand-edit the page to add new rides and delete old ones. The process didn't take all that long, but it did involve a lot of cutting, pasting, typing and formatting, which was tedious and repetitive.

It quickly dawned on me that I could automate the work. Being a computer programmer by trade, I naturally decided to write a program to do the job.

My first step was to set up a Microsoft SQL Server database to hold details of the bike rides and other forthcoming events. I then developed a small desktop application to maintain the data and to automatically generate formatted HTML code from it. I chose Microsoft Visual FoxPro 9.0 (VFP) for the job, mainly because of its excellent string-handling functions - just what's needed for generating HTML content. Figure 1 shows the application's main data-maintenance form.

Form for maintaining main events table

Figure 1: My initial desktop application used this form to maintain the main events table.

Once every three weeks or so, I would use the application to generate an up-to-date web page. My FoxPro program brought the events into a local cursor, which it then merged with a template to produce a finished HTML page. VFP is really fast at this sort of thing. Almost before I'd finish clicking the Generate Now button, I would see a message telling me the page was ready.

This approach brought some immediate benefits. In particular, it allowed me to concentrate on the site's content rather than its layout. It also made it much simpler to deal with recurring events. For example, Spokes (a Lothians-based cycling group) runs a ride on the first Sunday of every month. The details of that ride are pretty much the same each time, apart from the date and occasionally some other minor details. By adding a "clone" button to the form, I was able to generate a new record from an existing one, leaving only the date and the minor details to edit. This feature alone saved me a lot of time when updating the site.

Photos and quotes

As well as its central event listings, the main page includes a rotating sequence of photos taken on recent rides, plus a "quote of the day" - an enlightening or humorous quotation more-or-less related to cycling. It was an easy matter to extend the application to handle these items.

For the photos, I used a database table to store the image filenames and captions. Whenever I wanted to show a different picture, I only had to tell the program which one to include. The software would generate the relevant HTML code (that is, the <img> tags and the caption text) and add it to the page.

For the quote of the day, I wrote a simple JavaScript function. This stored the hard-coded quotes in an array, and returned a new one each day, using the day number as the array index.


Although the system saved me a good deal of time, it did have some drawbacks. First, I had to actually get round to generating and uploading the page. If I was ill or on holiday, the job didn't get done. Another problem was that the page was only as up to date as the last upload. As a result, events sometimes remained visible for weeks after they happened, and new events sometimes didn't appear until it was nearly too late.


To get round those problems, I've now rewritten the system as a server-based application. The concepts are the same, but both the application and the database are now located on the web server. Instead of generating the listings on a batch basis, it's now done on the fly, whenever a visitor arrives at the site. The software automatically selects the events that are due to take place over the next so-many weeks, and disregards those that have already happened. I can update the data whenever I hear of a new event, and no longer have to find time to generate the page or upload it to the server.

For this new system, I chose PHP for the programming language and MySQL for the database. I would like to have stayed with my original platform, but VFP, despite its versatility, is not a good tool for web-based systems. And I had to rule out SQL Server because of the relatively high hosting costs. (The cost of running the site comes out of my own pocket, although it's partially offset by a small income generated by advertising.)


A particular problem I faced in both the desktop and server systems was the need to apply formatting to the text. In particular, I wanted to add bold and italic styles, hyperlinks and mailto links, and special characters like accented letters. My original data-entry form used a standard HTML textarea control for the event text, and the only way to add formatting was to include hand-coded HTML tags, which was a nuisance.

I solved the problem by installing an extremely useful third-party component: the TinyMCE Editor from Moxiecode Systems ( This is essentially a plug-in replacement for the HTML textarea control, providing full support for WYSIWYG editing. It's written in JavaScript, and is available free of charge under an open-source licence.

Figure 2 shows the editor as it appears in my data maintenance form. Note the toolbar above the editing area. The control has a huge range of features - more than I'm ever likely to use - and comes with a highly configurable set of toolbars that give easy access to them. For my form, I greatly simplified the toolbar to reflect only the few functions I needed.

HTML form with TinyMCE Editor

Figure 2: The server-based version uses the TinyMCE Editor

One thing I don't use TinyMCE for is the formatting of mailto links. Many of the events listed on the page include the contact person's email address. I want to let visitors click on an address to bring up a message-composing window, but I'm aware of the dangers of including email addresses directly in the page. To deal with that, I wrote some PHP code that scans the event text for email addresses, wrapping any it finds in a call to a Javascript "obfuscator". That way, the visitor still gets the benefit of the mailto link, but a malicious web-crawler will only see the function call, not the address.

As well as being more convenient for myself and more up to date for my visitors, the PHP-based system has allowed me to add features to the site that would be difficult or inefficient to implement in plain HTML. Recent enhancements have included up-to-the minute weather icons and an interactive cycling quiz. The next task on my to-do list is a mobile version of the site - aimed at cyclists who want to use their iPhones or Blackberries to find out about forthcoming rides.

Over to you

This whole project is just a spare-time diversion for me - a relaxing break from my real work, which is developing commercial software and websites for businesses and other clients. If you could use some professional help with your own software project, please get in touch. Through my company, Mike Lewis Consultants Ltd, I offer programming, website design and general computer consultancy services, with expertise in Visual FoxPro, SQL Server, Crystal Reports, PHP and, of course, HTML.

If you're not interested in using my services, thank you anyway for reading this article, and do please try some of the excellent bike rides listed on the site.


Also on this site:

Organised rides
Up-to-date listing of all local rides.

Edinburgh Bike Paths: A Cyclist's Guide
How to get from A to B on two wheels.

Escape Edinburgh by Bike
These routes will get you out of the city quickly and safely.

I want to go on a bike ride but I haven't got a bike
How to hire a bicycle - or buy one second hand - in Edinburgh

The Edinburgh to St. Andrews (Lepra) bike ride
Everything you need to know about this popular event.

Browse all articles on this site

Recommended books about Visual FoxPro, SQL Server and related technologies.

Cycling Edinburgh is maintained by Mike Lewis.

Please contact me with your feedback, suggestions or details of events you would like me to include on this site. You can email me at:

Copyright Mike Lewis Consultants Ltd. and contributors, 2006 - 2013. You may copy short extracts from this page for non-commercial use, provided you acknowledge the source and include a link back to the original page. All other copying or re-publishing is prohibited.