Welcome to the last part in our three-part tutorial series on building a database-driven photo gallery system using PHP and MySQL.
In the first part of this series we looked at defining and setting up the structure of our database tables and briefly discussed how the different fields relate to each other. In the second part, we looked at actually writing the necessary SQL queries required to extract the information from the database. So what now?
If part 2 was about SQL, part 3 is about writing PHP to execute those queries and render the results returned from them. Although this may sound like programming, it’s really not that difficult and I will attempt to explain everything along the way. At this point you may wish to skip to the end and download the final source code examples.
Listing Categories
Following a similar pattern from part 2, our first task is to take the database connection file we created and include that in the main gallery index page. This is done using PHP’s require_once function to essentially merge the two files at runtime helping reduce duplicated code. Our connection file was named connection.php.inc and contained the basic information needed to allow PHP to communicate with MySQL, thus permitting queries to be executed and the results returned to the browser in a viewable form.
The code used to include a file inline inside another PHP file is very simple and looks like this:
<?php
require_once('connection.php.inc');
?>
Place this line as the first line of your HTML file (above the DOCTYPE tag). In fact, all queries will go above that part of the document because they need to be executed before the page is rendered in order for the actual content to be available.
The second step is to take the page used to render the thumbnail view of the individual photo categories. In order to produce clean, readable URLs, this should perhaps all be contained within a directory named galleries and the main thumbnail view in a file named index.php. Be sure to use the .php extension in order to ensure that the PHP parser will do its magic on the server end of things. Alternatively you can change the file extension to html provided you’ve instructed Apache to also parse HTML files.
In the index.php file, directly below the require_once directive, place the following code snippet:
<?php
mysql_select_db($database, $galleries);
$query_rsCategories = "--GET CATEGORY LIST QUERY--";
$rsCategories = mysql_query($query_rsCategories, $galleries)
or die (mysql_error));
$row_rsCategories = mysql_fetch_assoc($rsCategories);
$totalRows_rsCategories = mysql_num_rows($rsCategories);
?>
Since the specific queries were covered in part 2, the above code is using a simple descriptive placeholder. You can see the full code used in the tutorial source download.
Displaying the Category Thumbnails Returned
At this point, if requested, this page should successfully return a set of results but would essentially do nothing since there is currently no display code in the page.
There are a number of ways to structure the display code — tables, lists, etc. In this example, the display code is structured as a simple list. Each list item contains the category thumbnail preview wrapped by a link to display the photo viewer window and the category description. The code will loop through the results returned (1 iteration for each result returned).
<ul>
<?php do { ?>
<li><a href="view.php?category_id=<?php echo
$row_rsCategories['photo_category_id'];?>
&photo_id=<?php echo $row_rsCategories['photo_id'];?>" title="<?php echo
$row_rsCategories['category_desc']; ?>"><img src="/photos/<?php echo
$row_rsCategories['cat_preview']; ?>" alt="<?php echo
$row_rsCategories['photo_id'];?>">
<span> class="desc">
<?php echo $row_rsCategories['category_desc']; ?>
</span>
</a></li>
<?php } while ($row_rsCategories); ?>
</ul>
After executing the query it’s a good idea to close the connection to MySQL so information isn’t retained in memory unnecessarily. Just below the closing HTML tag on the page, add the following:
<?php mysql_free_result($rsCategories); ?>
That’s everything in the index.php page and it’s time to move on to the photo viewer page to display the large preview along with the associated photo metadata.
Displaying Photos And Meta Data
As outlined earlier this is a two file solution although the code used to build this photo gallery could be merged so that everything is included in the main index file. The second file, named view.php will be used to display the selected category of photos along with the large preview of each. As per the previous code samples, the required PHP code all belongs above the page’s DOCTYPE so that it is parsed and available prior to the page being rendered.
The first step is to again include the require_once statement to initiate the necessary database connection. The first query will take the two variables posted from the index page links and return the category information. The two variables being used are category_id and photo_id. This first query looks like:
SELECT description FROM categories WHERE id = "%s", $colname_rsCategory
The second query returns the actual photo information for a single photo under the specified category — such as the photo ID, filename and comment.
SELECT id, filename, comment FROM photos WHERE category_id = %s
AND id = %s ORDER BY id ASC LIMIT 1
The third and final query returns a data which can be used to create navigation links to browse a single category of photos.
SELECT photos.photo_num, photos.id FROM photos
WHERE photos.category_id = %s ORDER BY photos.id ASC
Each query uses the sprintf function which returns formatted strings. This is where the %s portion of the code comes from.
Putting it all Together
At this point all that’s left to do is wrap up the final pieces of display code, style the layout using CSS and start testing to make sure everything is working as expected. The remainder of this tutorial won’t focus on the visual design aspects but will cover the remaining pieces of display code in the HTML for the view.php file.
To display the category description, simply echo the results of the first query in the page using:
<?php echo $row_rsCategory['description']; ?>
Next, to display the category navigation, create a second list element and loop through the results from the second query, outputting a new list item for each result returned by the query. The code looks like this:
<ul class="photo_navigation">
<?php do { ?>
<li><a href="view.php?category_id=<?php echo
$HTTP_GET_VARS['category_id']; ?>&photo_id=<?php echo
$row_rsGetImages['photo_id']; ?>"><?php echo
$row_rsGetImages['num']; ?>
</a></li>
<?php } while ($row_rsGetImages); ?>
</ul>
Displaying the photo numbers is also quite easy. We’re going to use a table since technically this is tabular information and semantically we’re not breaking any rules. We also need to apply a horizontal loop to generate the rows and columns for the table. Each item in the table will link to one of the photos returned from the third query. The code looks like this:
Lastly, output the large photo itself along with the associated comment. This is simply a matter of outputting the results of the third query.
<img src="photos/<?php echo $row_rsFirstImage['filename']; ?>"
alt="<?php echo $row_rsFirstImage['comment']; ?>" class="photo">
<div class="photo_comment">
<?php echo $row_rsFirstImage['photo_comment']; ?>
</div>
Next, we need to display the photo itself and the associated comment meta-data for the photo. Again, this is fairly basic and is just a matter of echoing the results of the query to the page in the appropriate places. It looks something like this:
As in the index.php file, the last bit of code required is to close the connection to MySQL. Below the closing HTML tag, add the following:
<?php
mysql_free_result($rsCategory);
mysql_free_result($rsFirstImage);
mysql_free_result($rsGetImages);
?>
And… that’s it. Visually it may not look like much but you now have a simple photo gallery which can be customized to your liking. You can download the full source code for this tutorial below. If you have suggestions, code improvements, additional features or any questions on completing the tutorial, please leave a note in the comments.
Source Code Downloads
Download Tutorial Example Source Code - ZIP File
