PHP, MySQL & Pagination – With Tags

Disclaimer

The instructions/steps/scripts/methods given below worked for me running CentOS. It may very well work for you on other linux distributions, Red Hat-like or otherwise. Please note that if you decide to use these instructions on your machine, you are doing so entirely at your very own discretion and that neither this site, sgowtham.com, nor its author is responsible for any/all damage – intellectual or otherwise.

Why?

This is in continuation of my previous blog entry. In that, I shared my scripts/codes to generate a decent looking, well-paginated photo gallery using PHP and MySQL. In this entry, I will focus on enhancing parts of those scripts and share the required code modification(s). For the sake of completeness, I will go over some of the previously mentioned parts as well:

Requirements

Just like in the previous article, you need to have access to a web server (preferably a linux one running Apache) with ImageMagick, PHP and MySQL installed. And let’s assume that we are still dealing with our good old photo gallery. Our aim is to:

1. Display about 20 thumbnails per page (you can change this number too) with each of them being a link to that particular image
2. Below each thumbnail, display the corresponding title.
3. Display the corresponding picture, in the same page, when clicked on any given thumbnail.
4. Provide viewers an option to display thumbnails (and corresponding images) that belong to a specific tag.

Since such a set up is more likely to showcase one’s best pictures, we will display the thumbnails in latest first fashion.

Directory Structure

I wrote / implemented this on a Red Hat Enterprise Linux server, and by default, the web documents are stored in /var/www/html. To keep the discussions generic, I will refer to this location as $DocumentRoot (which is a standard directive in Apache, whose value can be found in httpd.conf). So, let us assume that

1. our gallery will be located in $DocumentRoot/showcase
2. original images will be stored under $DocumentRoot/showcase/default
3. thumbnails will be stored under $DocumentRoot/showcase/thumbnails

Preparing Images

Before we do anything else, we need to get our images ready for display. In an attempt to do so, transfer the original (or edited in GIMP, Photoshop, etc.) pictures (directly from your camera or from some other folder in your computer) to $DocumentRoot/showcase/default. Use the bash script below ($DocumentRoot/showcase/default/arrange.sh) to resize the original images and create thumbnails.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

#! /bin/bash
 
# Store this script as $DocumentRoot/showcase/default/arrange.sh
# BASH script to resize the original images and create thumbnails.
# Also adds a copyright notice to the resized images.
# Uses 'convert' utility that comes part of ImageMagick
 
for x in `ls *.jpg`
do
  if [ "$x" != "" ];
  then
    echo Working on $x
    convert -size 720x540 $x  -resize 720x540 ../$x
    convert -size 100x75  $x  -resize 100x75 ../thumbnails/$x
    convert ../$x -fill 'white' -box '#00000080' -gravity southwest \\
    -pointsize 9 -annotate 270x270+15-7 '   © Your Name ' ../$x
  fi
done

MySQL Set Up

Like in the previous article, we will only store the image name along with some other useful data in our database. But, we will add few more fields to incorporate the new changes. So,

1. Create a MySQL database – call it PhotoGallery. Make sure you have at least INSERT, DELETE, UPDATE, SELECT previleges on that database. We can use the same database created in the previous article.

   1	CREATE DATABASE `PhotoGallery`;

   CREATE DATABASE `PhotoGallery`;

2. There can be multiple tables under one database. So, create a table in this database – call it MyShowcase. Use the following syntax to create it.

   1 2 3 4 5 6 7 8 9 10 11

   DROP TABLE IF EXISTS `PhotoGallery`.`MyShowcase` ;   CREATE TABLE `PhotoGallery`.`MyShowcase` ( `ID` mediumint( 9 ) NOT NULL AUTO_INCREMENT , `IFileName` varchar( 100 ) NOT NULL default '', `ITags` varchar( 150 ) NOT NULL default '', `ITitle` varchar( 30 ) NOT NULL default '', `IInfo` text, UNIQUE KEY `ID` ( `ID` ) , UNIQUE KEY `IFileName` ( `IFileName` ) ) ENGINE = MYISAM DEFAULT CHARSET = latin1;

   DROP TABLE IF EXISTS `PhotoGallery`.`MyShowcase` ; CREATE TABLE `PhotoGallery`.`MyShowcase` ( `ID` mediumint( 9 ) NOT NULL AUTO_INCREMENT , `IFileName` varchar( 100 ) NOT NULL default '', `ITags` varchar( 150 ) NOT NULL default '', `ITitle` varchar( 30 ) NOT NULL default '', `IInfo` text, UNIQUE KEY `ID` ( `ID` ) , UNIQUE KEY `IFileName` ( `IFileName` ) ) ENGINE = MYISAM DEFAULT CHARSET = latin1;

3. Let me explain the structure. It’s very very simple and has only two more fields than our previous table – ID is ImageID, IFileName is the image file name, ITitle is the image title, ITags will contain tags associated with the image, and IInfo will contain some text which might explain what the image is about.
4. Entering one image information at a time into the datbase can be quite painful. So, prepare a SQL file like the one shown below and save it as MyShowcase.sql:

   1 2 3 4

   INSERT INTO PhotoGallery.MyShowcase VALUES ('', 'dsc_3537.jpg', 'All, Nature', 'Sunrise', 'Sunrise on Sunset Blvd, Los Angeles'), ('', 'dsc_3539.jpg', 'All, Life', 'Memorable Drive', 'Driving along Pacific Coast Highway, CA'), ('', 'dsc_3540.jpg', 'All, Sports', 'Riding Waves', 'Surfing in Malibu, CA');

   INSERT INTO PhotoGallery.MyShowcase VALUES ('', 'dsc_3537.jpg', 'All, Nature', 'Sunrise', 'Sunrise on Sunset Blvd, Los Angeles'), ('', 'dsc_3539.jpg', 'All, Life', 'Memorable Drive', 'Driving along Pacific Coast Highway, CA'), ('', 'dsc_3540.jpg', 'All, Sports', 'Riding Waves', 'Surfing in Malibu, CA');

5. Import this data into MySQL using the following command:

   mysql -u YOUR-MySQL-USERNAME -p -h localhost < MyShowcase.sql

   mysql -u YOUR-MySQL-USERNAME -p -h localhost < MyShowcase.sql

6. Enter the password when prompted. If there are no error messages, you are good!

Paginating with PHP, with Tags

OK – we got the images resized, thumbnails created, database ready with a table contaning all the required data. So, the last part is to display these images, and for this, we will make use of PHP. I will split up the PHP file, for the sake of understanding and explanation:

Part 1: $DocumentRoot/showcase/db.inc

This file will contain all the information regarding connecting to MySQL database, etc. It could have been included in the main file itself but it’s a good idea to keep it separate.

1

<!--?php # $DocumentRoot/showcase/db.inc # Contains MySQL database connection related information $host = "localhost"; $dbuser = "YOUR-MySQL-USERNAME"; $dbpasswd = "YOUR-MySQL-PASSWORD"; $database = "PhotoGallery"; $connect = mysql_connect($host, $dbuser,$dbpasswd) or die(mysql_error()); mysql_select_db($database,$connect) or die(mysql_error()); ?-->

Part 2: $DocumentRoot/showcase/index.php

How will this PHP script know when to display thumbnails and when to display an individual image? This script uses three variables – imageid, page and imagetag – which can be passed along as options to the URL. Depending on the value of these three variables, script decides what to display. Let’s look at part of the code ($DocumentRoot/showcase/index.php):

1
2

 
<!--?php # MySQL database connection file include('db.inc'); # Get the image ID and make sure it's an integer $imageid = $_GET[id]; $imageid = round($imageid); # Get the page ID and make sure it's an integer # If page ID is not set, set it to 1 if( empty($_GET['p']) ){ $page = 1; } else { $page = $_GET['p']; $page = (int)$page; } # Get the image tag # If image tag is not set, set it to All (default tag) $imagetag = $_GET[c]; if(empty($imagetag)) { $imagetag = "All"; } # Calculate the total number of pages, based on how many # images will be displayed per page and total number of # images in the database. $sql0 = "SELECT ID FROM MyShowcase ITags "; $sql0 .= "LIKE '%$imagetag%' ORDER BY ID DESC"; $result0 = mysql_query($sql0); $maximg = mysql_num_rows($result0); # Total number of images $perpage = 20; # Maximum images per page $tpages = ceil($maximg / $perpage); # Total number of pages ?-->

I presume the above chunk of code is not too hard to understand. Next small chunk will display smart page title – if the selected image tag is All, we don’t need to display the tag. Also, we need to give the viewers an option to pick one of the available tags (and you can extend the list to your requirements):

My Photo Showcase

Take a pick : All | Nature | Life | Sports

For the next part, we will assume that the imageid variable is not set. Thus, each page will display contain 20 thumbnails and the chunk of code below shows how to build Previous and Next links.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

<!--?php 
  # Build the previous link
  if (empty($imageid)){
    echo &lt;&lt;&lt; EOF
    &lt;/p&gt;
&lt;p align="right"&gt;
    &lt;font size="1"&gt;
    EOF;
 
    if($page &gt; 1){
      $prev = ($page - 1);
      echo "&lt;a href=\"/showcase/?p=$prev\"&gt;Previous&lt;/a&gt; &amp;nbsp;";
    }
 
    # Build the next link
    if($page &lt; $tpages){
      $next = ($page + 1);
    echo "&amp;nbsp; &lt;a href=\"/showcase/?p=$next\"&gt;Next&lt;/a&gt;";
    }
 
  echo &lt;&lt;&lt; EOF
  &lt;/font&gt;
  &lt;/p&gt;
&lt;p&gt;
  EOF;
  }
?-->

OK – this above chunk was simple too. Now that we have next and previous links built, let’s move on to display the thumbnails. Each thumbnail will act as a link to its corresponding image. Below each thumbnail, the corresponding title will also be displayed. We will use some simple arithmetic to decide the first and last image ID based on value of page ID.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

<!--?php 
  # If image ID is empty, display N number of thumbnails
  if(empty($imageid)) {
 
    # Limits for image ID (Upper and Lower) per page
    $limits   = 'LIMIT ' .($page - 1) * $perpage .',' .$perpage;
    $sql1     = "SELECT ID, IFileName , ITitle  FROM MyShowcase ";
    $sql1    .= "WHERE ITags LIKE '%$imagetag%' ORDER BY ";
    $sql1    .= "ID DESC $limits";
    $result1  = mysql_query($sql1);
    $idetails = mysql_fetch_array($result1);
 
    do {
      printf("&lt;a href=\"/showcase/?id=%s&amp;c=%s\"&gt;&lt;img src=\"/showcase/thumbnails/%s\" hspace=\"5\" vspace=\"10\" alt=\"Thumbnails\"&gt;&lt;/a&gt; &lt;/p&gt;
&lt;p&gt;%s&lt;/p&gt;
&lt;p&gt;", $idetails['ID'],
      $imagetag, $idetails['IFileName'], $idetails['ITitle']);
    } while ($idetails = mysql_fetch_array($result1));
  }
?-->

Cool – now to the last bit. If the image ID is set (i.e., when some one clicks on any thumbnail), we need to display just that one particular image. And, the page should also provide links to the Next and Previous images. Chunk of code below will do just that:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

<!--?php # If image ID is not empty, display the corresponding image if(!empty($imageid)) { $sql2 = "SELECT * FROM MyShowcase WHERE ID ='$imageid' "; $sql2 .= "AND ITags LIKE '%$imagetag%'"; $result2 = mysql_query($sql2); $idetail = mysql_fetch_assoc($result2); # Extract information from the array, idetail $id = $idetail['ID']; $ifilename = $idetail['IFileName']; $ititle = $idetail['ITitle']; $iinfo = $idetail['IInfo']; # Display the *intelligent* Previous and Next links # Though it looks like we are doing too many SQL queries # into our database, MySQL is fast enough to handle our # request and will intelligently pick the next (previous) # available image ID. $psql = "SELECT ID FROM MyShowcase WHERE ID "; $psql .= "&gt; '$imageid' AND ITags LIKE '%$imagetag%' ";
    $psql     .= "ORDER BY ID DESC LIMIT 1";
    $prev_row  = mysql_fetch_assoc(mysql_query($psql));
    $pid       = $prev_row['ID'];
 
    $nsql      = "SELECT ID FROM MyShowcase WHERE ID ";
    $nsql     .= "&lt; '$imageid' AND ITags LIKE '%$imagetag%' ";
    $nsql     .= "ORDER BY ID ASC LIMIT 1";
    $next_row  = mysql_fetch_assoc(mysql_query($nsql));
    $nid       = $next_row['ID'];
 
    print "&lt;/p&gt;
&lt;p align=\"right\"&gt;";
    print "&lt;font size=\"1\"&gt;";
    print "&lt;a href=\"/showcase/?id=$pid\"&gt;Previous&lt;/a&gt; |";
    print " &lt;a href=\"/showcase/\"&gt;Home&lt;/a&gt;";
    print " | &lt;a href=\"/showcase/?id=$nid\"&gt;Next&lt;/a&gt;";
    print "&lt;/font&gt;";
    print "&lt;/p&gt;
&lt;p&gt;";
 
    # Display the image, title and its description
    print "&lt;img src=\"$ifilename\" alt=\"Showcase\" "; print "title=\"Showcase\"&gt;";
    print "&lt;/p&gt;
&lt;h3&gt;$ititle&lt;/h3&gt;
&lt;p&gt;";
    print "&lt;/p&gt;
&lt;p&gt;";
    print "$iinfo";
    print "&lt;/p&gt;
&lt;p&gt;";
  }
?-->

That’s it! A working example of the above code is here. Also, all the files associated with this write up are below:

arrange.sh | MySQLTable.sql | MyPhotos.sql | db.inc | index.php

Just like the previous article, I am over 100% sure that you probably would find a better set of scripts and/or a more efficient way to get the same things done. But, if you do like it and think that it meets your requirements, feel free to use it. In my next entry, I will probably write about extracting and displaying EXIF data for these (digital) images on the fly. Until then, your comments/criticisms are very welcome.