How to make a Photo Database

Thursday, October 11, 2001

What you need

I store all my photos in a bunch of folders, just like regular file storage. I keep a small, medium, large, and huge version of each. Then, I add information to a database: title, subject, location, photographer, etc., and the path of the file.

So, when you open viewer.asp?ID= the page looks up that ID number in the database, and builds the HTML to show you that particular photo. But, the photos are not stored in the database, just the path and file name.

This lets me also build lists of photos, based on keyword searches, or by path location. So, you could list all the photos in the folder \Beach, or you could simply do a search for all photos that contain the word "surf" whether or not they're in that folder.

You could implement a system like this on your web server with an Access database and a ASP pages; or whatever db / scripting language your server supports.

The hard part is taking the time to add all the pertinent information about the photos, you have to know what's in the picture, and also being descriptive enough with the text to have it match what you'll eventually want to search for.

The other hard part is making multiple version of the photos. For your case, what I would do is make just a thumbnail version and a full version. I recommend making the thumbnails 200 pixels wide/tall (whichever is longer).

How to build it

  1. Make a new database table with the fields you want to store. The more you store the more of a pain it will be to fill. I recommend:
    • Title
    • Description
    • Location
    • Photographer
    • Date of photo
    • Path to photo (from a given "photos" folder, not from the drive letter)
    • Filename (don’t use any spaces or punctuation in the names)
    • Autonumber (let Access generate a unique ID for each photo)
  2. As you copy photos into the "photos" folder, add a record for each of them into the database. Be sure you take the time now to generate a thumbnail version for each full version. Pick a standard convention for differentiating between the thumbnail and full versions, like end the name with "_T"
  3. Then you build the "view a single photo" page. The page expect to be passed the parameter ID=, which it uses as a parameter in the query to look up the photo information. It builds the page with the database info, using the path/filename inside the image tag: <IMG src="<path><filename>"> The query might look something like this: SELECT * FROM Photos WHERE ID=[id]
  4. For the search page, you do the same thing, looping through multiple records, and expecting a keyword instead of an ID. This query might look like this: SELECT * FROM Photos WHERE Title LIKE '%strKeyword%' OR Description LIKE '%strKeyword'
    You could add as many fields to search as you like. The LIKE means the field should contain that word, and the % means the same as an *, it's a wildcard. If you wanted to match something exactly, leave them out. For starting with (useful for including everything in a given path downward), leave off the end one.

3 Comments