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.
<?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()); ?>
[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
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
anotherfile01.jpg but not
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 —
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!