PHP, MySQL & Pagination

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?

Looking at the title, you (if you are one of those geeks or nerds) might be wondering why yet another article about stuff on which there already exists a plethora of articles. You are more than welcome to wander off and do something better with your time. However, you (even if you are not a geek or nerd) are more than welcome to read through and adapt it to meet your requirements. As to why I wrote my own, don’t really have an exact answer – of the many availalbe ones on the net, I didn’t (couldn’t) find one that would meet all my requirements. That being said, this script (or set of scripts) will probably not meet all your requirements either.

Requirements

Needless to mention, you need to have access to a web server (preferably a linux one running Apache) with ImageMagick, PHP and MySQL installed (I believe you get all these installed, by default, if you opt for full installation or server edition of almost any linux distribution). And you need to have something that would require pagination. As a case study, let’s assume that we intend to design a 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. Display the corresponding picture, in the same page, when clicked on any given thumbnail.

So, let’s roll.

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/gallery
2. original images will be stored under $DocumentRoot/gallery/default
3. thumbnails will be stored under $DocumentRoot/gallery/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/gallery/default. Use the bash script below ($DocumentRoot/gallery/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/gallery/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

There is an eternal controversy between two different groups of people – one, who think that it’s a good idea to store the images as BLOB (Binary Long Object) in a MySQL database and the other, who think that such a method is not a great idea. Without spending too much of time in understanding and/or trying to resolve this controversy, let’s move on – we will only store the image name along with some other useful data in our database. So,

1. Create a MySQL database – call it PhotoGallery. Make sure you have at least INSERT, DELETE, UPDATE, SELECT previleges on that database.

   1	CREATE DATABASE `PhotoGallery`;

   CREATE DATABASE `PhotoGallery`;

2. Create a table in this database – call it MyGallery. Use the following syntax to create it.

   1 2 3 4 5 6 7 8 9

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

   DROP TABLE IF EXISTS `PhotoGallery`.`MyGallery` ; CREATE TABLE `PhotoGallery`.`MyGallery` ( `ID` mediumint( 9 ) NOT NULL AUTO_INCREMENT , `IFileName` varchar( 100 ) 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 – ID is ImageID, IFileName is the image name 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 MyPhotos.sql:

   1 2 3 4

   INSERT INTO PhotoGallery.MyGallery VALUES ('', 'dsc_3537.jpg', 'Sunrise on Sunset Blvd, Los Angeles'), ('', 'dsc_3539.jpg', 'Driving along Pacific Coast Highway, CA'), ('', 'dsc_3540.jpg', 'Surfing in Malibu');

   INSERT INTO PhotoGallery.MyGallery VALUES ('', 'dsc_3537.jpg', 'Sunrise on Sunset Blvd, Los Angeles'), ('', 'dsc_3539.jpg', 'Driving along Pacific Coast Highway, CA'), ('', 'dsc_3540.jpg', 'Surfing in Malibu');

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

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

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

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

Paginating with PHP

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/gallery/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/gallery/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/gallery/index.php

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

My Photo Gallery

I presume the above chunk of code is not too hard to understand. 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=”/gallery/?p=$prev”&gt;Previous&lt;/a&gt;  ”;
    }
 
    # Build the next link
    if($page &lt; $tpages){
      $next = ($page + 1);
    echo “  &lt;a href=”/gallery/?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. We will use some simple arithmatic 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

<!--?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 FROM MyGallery ORDER BY ID ASC $limits";
    $result1  = mysql_query($sql1);
    $idetails = mysql_fetch_array($result1);
 
    do {
      printf("&lt;a href=\"/gallery/?id=%s\"&gt;&lt;img src=\"/gallery/thumbnails/%s\" hspace=\"5\" vspace=\"10\" alt=\"Thumbnails\"&gt;&lt;/a&gt;", $idetails["ID"], $idetails['IFileName']);
    } 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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

<!--?php 
  # If image ID is not empty, display the corresponding image
  if(!empty($imageid)) {
    $sql2    = "SELECT * FROM MyGallery WHERE ID ='$imageid'";
    $result2 = mysql_query($sql2);
    $idetail = mysql_fetch_assoc($result2);
 
    # Extract information from the array, idetail
    $id        = $idetail['ID'];
    $ifilename = $idetail['IFileName'];
    $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.
    print "&lt;/p&gt;
&lt;p align=\"right\"&gt;";
    print "&lt;font size=\"1\"&gt;";
 
    if (($imageid &lt;= $maximg) and ($imageid &gt; 0)) {
      $psql      = "SELECT ID FROM MyGallery WHERE ID ";
      $psql     .= "&lt; '$imageid' ORDER BY ID DESC LIMIT 1";
      $prev_row  = mysql_fetch_assoc(mysql_query($psql));
      $pid       = $prev_row['ID'];
      print "&lt;a href=\"/gallery/?id=$pid\"&gt;Previous&lt;/a&gt; |";
    }
 
    print " &lt;a href=\"/gallery/\"&gt;Home&lt;/a&gt;";
 
    if($imageid &lt;= $maximg) { $nsql = "SELECT ID FROM MyGallery WHERE ID :; $nsql .= "&gt; '$imageid' ORDER BY ID ASC LIMIT 1";
      $next_row = mysql_fetch_assoc(mysql_query($nsql));
      $nid      = $next_row['ID'];
    }
 
    if($nid &gt; 0){
      print " | &lt;a href=\"/gallery/?id=$nid\"&gt;Next&lt;/a&gt;";
    }
    print "&lt;/font&gt;";
    print "&lt;/p&gt;
&lt;p&gt;";
 
    # Display the image, and its description
    print "&lt;img src=\"$ifilename\" alt=\"Gallery\" title=\"Gallery\"&gt;";
    print "&lt;/p&gt;
&lt;p align=\"center\"&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

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. It’s simple enough and well commented (I think) – it’s also quite easier to extend the MySQL & PHP code to include more fields, etc. When time permits, I will post another write up some day. Until then, have fun using these and/or commenting on them :)