In part two of this tutorial series on building your own database-driven photo gallery we’ll cover the SQL queries needed to return the various pieces of information used to generate the gallery display and the individual photo pages.

I’m assuming at this point that you already have MySQL and Apache installed and configured. For more information, see their respective web sites as well as to download the required software for your platform.

Connecting to MySQL

As you might recall from the first part, the database tables allow simple categorization of photos to allow viewing small segments of all the available photos listed in the database.

The first thing needed is to create the actual database connection which will facilitate communication between PHP and the MySQL database which will run the queries and return the results to be rendered by the Apache web server and returned to the browser.

The connection information should generally be separated from the real guts of the application for both clarity and more importantly because it adds a layer of security and could help prevent the access credentials from accidentally being compromised. You may also want to add the necessary information to your Apache .htaccess file(s) to protect this file from being viewable, depending on your level of paranoia. But really, it is a good idea.

This file, named connection.php.inc will contain the necessary database access credentials and set up some variables we can use elsewhere to reliably connect to MySQL and execute queries against the database. This file won’t be used until part 3, but it’s valuable to create it now.

To create the file, using your preferred plain text editor (BBEdit, pico, vim, Dreamweaver, etc.) copy the contents below.

<?php
  # File name: connection.php.inc
  $hostname = "localhost";
  $database = "photo_gallery";
  $username = "YOUR_DB_USERNAME";
  $password = "YOUR_DB_PASSWORD";
  $galleries = mysql_pconnect($hostname, $username, $password) or die (mysql_error());
?>

Replace the [YOUR_DB_USERNAME] and [YOUR_DB_PASSWORD] with the appropriate details for your system.

Return Photo Categories

The first query we need to use returns a list of the categories listed in the database along with the auxiliary information used to display each category along with a link to launch the photo viewer. For the time being we’ll just focus on the SQL and leave the presentation code to part 3.

SELECT DISTINCT photos.id, photos.category_id, categories.preview, categories.description 
FROM photos, categories WHERE photos.category_id = categories.id AND photos.filename 
LIKE '%01.jpg' ORDER BY filename ASC

This first query returns the id, category_id, preview and description for each category. The two ID values are passed on to the viewer queries to locate the correct photos for the selected category. A small preview is displayed for each category using the preview field along with the description data for the category.

The query makes use of a simple wildcard to locate the appropraite preview images. The % character is used to ensure the query returns anything matching filename01.jpg or anotherfile01.jpg but not morefiles1.jpg.

Everything needed to create a simple category-based thumbnail preview for each category of photos will be returned by that one query and we can now move on to the main photo viewer file — view.php.

Photo Enlargement

This second file (view.php), the photo viewer is built based on three queries. The first returns the category descriptions, the second returns the large photo to be displayed and the last will return a list of all the photos associated with the selected category.

Return the Category Name

To return the selected category information, we are passing the category_id value from the main thumbnails page which is reflected in the query.

SELECT description FROM categories WHERE category_id = "%s", $colName_rsCategory

Return a single Large Photo

To find and return a single full-size photo and its associated metadata, the SQL query requires two values — the category_id and the photo_id from the thumbnails display page. Because we only want to render a single photo, we also need to restrict the results returned to a single entry.

SELECT id, filename, height, width, comment 
FROM photos WHERE photos.category_id = %s AND id = %s 
ORDER BY id ASC LIMIT 1

This query is executed each time a user clicks on a link from the returned list of photos in a selected category.

Return a Category’s Photos

The final query returns a list of every photo associated with a single category. As before, the category_id value is used to return the relevant results.

SELECT photo_gallery.photo_num, photo_gallery.photo_id 
FROM photo_gallery WHERE photo_gallery.photo_category_id = %s 
ORDER BY photo_gallery.photo_id ASC

Well, that’s it for now class. In part three we’ll tie everything together and show how it all works along with a few samples of the presentation code used here. At that point I’ll also make source code available for the gallery project. See you next time!