Scott Boms

Database-Driven Gallery Tutorial (Part 1)

Welcome to part one of a three part tutorial series on how to build a dynamic, database-driven image gallery. You should be able to repurpose these instructions based on a different visual design since I’m not going to cover anything specific with regards to the visual aspects of the layout.

The tutorial has been broken down as three pieces for simplicity. Although we’ll only be dealing with two files and I could combine all of the back-end scripts as one file, for aesthetic reasons I’ll keep things separate. Let’s get started…


To complete the tutorial, you will need to have two pieces of software installed and running on your computer or a server somewhere. For the database component, you will need the community edition of MySQL installed. Version 4.0 or newer should all work. For the web server, I recommend using the Apache web server, included out of the box with Mac OS X. You will also need to ensure that the PHP module for Apache is enabled and configured appropriately. Follow any provided documentation for specifics on security-related issues.

Setting up the MySQL Database

Before doing anything else, the database source needs to be created for this tutorial. To keep things easy, I’ll also assume you set up phpMyAdmin to manage your databases.

  1. Login to phpMyAdmin in your web browser.
  2. Create a new database and name it photo_gallery. We’ll make reference to this later once we start developing the SQL queries that will bring things to life.
  3. Select the database you just created from the pop-up menu or listing on the left sidebar of phpMyAdmin. In the right frame, click on the SQL tab. This will open up the SQL editor view.
  4. To add the necessary tables to the photo_gallery database, copy the SQL queries below into the SQL editor window and press the Go button.

    CREATE TABLE categories (
      id int(11) unsigned NOT NULL auto_increment,
      description varchar(100) NOT NULL default '',
      preview varchar(64) NOT NULL default '',
      PRIMARY KEY (id)
      ) TYPE=MyISAM COMMENT='Gallery Categories';
    CREATE TABLE photos (
      id int(11) unsigned NOT NULL auto_increment,
      filename varchar(64) NOT NULL default '',
      comment varchar(255) default NULL,
      category_id int(11) unsigned NOT NULL default '0',
      PRIMARY KEY (id)
      ) TYPE=MyISAM COMMENT='Gallery Images';

    Assuming the SQL is executed successfully, you can close the SQL editor window.

  5. Now that the database tables have been created, you’re ready to start adding data. Start by creating a series of categories. Enter a description for each. The required ID field will be automatically created since the table is set to auto-increment the category_id field.

  6. Once you’ve created a few categories, you can start adding photos and assigning them to the previously created categories. The photo_filename field should contain the actual name of the image file including the file extension (eg. .jpg, .gif, .png). The photo_comment field is used to add a short description of the photo and the category_id field is a foreign key field and should contain the appropriate ID value from the categories table. This allows us to provide a simple category mechanism, though it currently only allows assignment of a single category.

Questions? Stuck?

If you get stuck, leave a comment and I will (within reason) try to provide adequate assistance.