Serving portfolios on the web

using a relational database to make life easier

I hate working with HTML. Don't get me wrong, I enjoy page layout. I've spent many happy hours behind the wheel of Quark Xpress and when I put together my first web site I was convinced it would be just like creating a brochure in Quark. I wasn't alone in this delusion. When the appeal of the world-wide-web became apparent to the masses every design house in the country added web design to their list of services without a second thought. The web promises so much, and from the point of view of a designer it has yet to deliver. I can design a page that looks splendid on my Microsoft browser only to discover with horror that I left a quote mark out and Netscape has been rendering my pages with neon green backgrounds. (if this page looks really bad on your browser please, let me know) The rift left by the browser war between Netscape and Microsoft is still felt with every html tag we type. My first site was a simple online portfolio. I wanted to save money by directing people to an online portfolio rather than sending out expensive prints and in this respect it was a success. The problem was that it was designed like a brochure. I had about 70 individual photos to display and as a result I had 70 html pages that were identical in every respect except for the the photo being displayed. To make matters worse, each page had five thumbnails pointing to other photos. If I wanted to update a single photo I needed to manually edit five html pages. As a friend of mine likes to say, "stupid, stupid, stupid!" I never wanted to update the portfolios with new work because, as I've said, I hate dealing with html. If I wanted to add a new portfolio, I would have needed to redesign the entire site. It was a hassle

The portfolio section of this site now consists of between 200 and 400 photos at any time. They are all being served from one perl script. If I discover, as I'm sure I will, that I botched an html tag somewhere, I will edit one page and the problem will be fixed globally. This is made possible by separating the information being served from the layout. All of the information about the photos such as captions, filenames, and locations is stored in a relational database. A lightweight but efficient one: msql. When a page is requested a simple perl script is invoked that sends an SQL query to the database asking for the information, inserts it in the page, and sends it to you. I thought this would be difficult, after all people make six-figure salaries doing this; it isn't. I'm not a computer geek—really I'm not—and I was able to figure out SQL in a day. Although I'm sure administering a giant Oracle database is a much different challenge, basic SQL is simple and basic SQL is all you need to get started. I'm not going to try and teach you the details; I'm really no expert. In fact, any undergraduate computer science major would laugh at the first glance of one of my scripts, but this is just the point, I'm no expert and I have been able to use this technology to make my life a little easier. The first chapter in almost any database book will show you how to set up database tables, and teach you enough SQL to begin. Here's an example of SQL's simplicity: if I want to get the caption for a photo, let's say photo number 330, I send this to the database:

SELECT caption FROM images WHERE id=330.

If I need to update this caption I send this:

UPDATE images SET caption='Hoh Rain Forest on the trail to Blue Glacier on Mt. Olympus' WHERE id=330.

People go to school for this? Not only is it easy and powerful, the software is free. Most web hosts offer mysql, msql or a comparable database package that you can use.

Of course you don't want to spend your time typing SQL statements into a terminal everytime you need to edit a caption. You'd rather be hiking so you write a simple perl script that spits out some html that lets you click on a link, or fill out a form when you need to organize photos or edit information. If I want to edit image information a 12-line perl script spits out a list of all the images with links to click if I want to edit, delete or add information. For instance, if I would like to edit the information contained in one of the portfolios a perl script sends me a page that looks like this

From here I can change any of the information served by the database. When I click submit, the appropriate SQL is sent to the database and the results will be instantly available to the portfolios. Best of all I don't need to wade through a single html tag. Almost every bit of information in the portfolios is handled this way. The images, the meta-tags, the titles, the links to the other portfolios, in short, anything that is not a design element is coming from a database and anything that is a design element is located in one file.

Now this is no yahoo or google. This site gets modest traffic at best and would soon choke if things started getting really busy. The database I am using is msql. The feature I like best about msql is that it costs me nothing. Free. Msql has traded some of the features that giant databases like Oracle have for simplicity and speed. It only supports a subset of SQL and doesn't support some niceties like transactions. The upside is that it requires few resources and web hosts don't mind you running it on their server. It really is a fast little database; it needs to be because it has a single-threaded server. Although it handles multiple connections, it queues each request and takes care of them one at a time. If I started getting tons of traffic I might have a problem because the database would not be able keep up with the requests. I haven't had this problem yet and I don't foresee it any time soon. If the entire world suddenly wanted to see my photos it would be rather simple to switch database engines; in fact I would only need to change a single statement in a couple of perl scripts.

Although it takes a little work to figure out it isn't difficult. After all, I studied music in school and work as a photographer and it seems to be working. All of the software required for this site can be had for free: perl-free, apache-free, msql-free. The people that develop this stuff are saints. So stop typing all that html and go take some pictures.

A couple of resources to get you started.

O'Reilly Books
MySql & mSQL by Randy Jay Yarger, George Reese & Tim King.
After the first 25 pages you will know enough to get started. It has great information about interfacing databases with web sites. This book also has a very useful reference section and chapters addressing perl, python, and php.

Programming Perl by Larry Wall, Tom Christiansen & Randal Schwartz.
The definitive perl reference also know as the "Camel" book. Everything you need to know and more. If you are just getting started you might find Randal Schwartz's Learning Perl a better introduction.

Hughes Technologies. The guys that made mSQL

Philip and Alex's Guide to Web Publishing. Philip Greenspun has made "database-backed website" a houshold term among photographers through his very popular Photo.net. The book is a good read and it's free online.