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`;
  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;
  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');
  5. Import this data into MySQL using the following command:
    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
2
3
4
5
6
7
8
9
10
11
<?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):

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
<html>
<body>
 
<h2>My Photo Gallery</h2>
 
<?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;
  }
 
  # 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 MyGallery";
  $result0 = mysql_query($sql0);
  $maximg  = mysql_num_rows($result0); # Total number of images
  $perpage = 20;                       # Maximum images displayed per page
  $tpages  = ceil($maximg / $perpage); # Total number of pages
?>

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
<?php
  # Build the previous link
  if (empty($imageid)){
    echo <<< EOF
    <p align="right">
    <font size="1">
    EOF;
 
    if($page > 1){
      $prev = ($page - 1);
      echo “<a href=”/gallery/?p=$prev”>Previous</a>  ”;
    }
 
    # Build the next link
    if($page < $tpages){
      $next = ($page + 1);
    echo “  <a href=”/gallery/?p=$next”>Next</a>”;
    }
 
  echo <<< EOF
  </font>
  </p>
  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
16
17
<?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("<a href=\"/gallery/?id=%s\"><img
      src=\"/gallery/thumbnails/%s\" hspace=\"5\" vspace=\"10\"
      alt=\"Thumbnails\"></a>", $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
53
<?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 "<p align=\"right\">";
    print "<font size=\"1\">";
 
    if (($imageid <= $maximg) and ($imageid > 0)) {
      $psql      = "SELECT ID FROM MyGallery WHERE ID ";
      $psql     .= "< '$imageid' ORDER BY ID DESC LIMIT 1";
      $prev_row  = mysql_fetch_assoc(mysql_query($psql));
      $pid       = $prev_row['ID'];
      print "<a href=\"/gallery/?id=$pid\">Previous</a> |";
    }
 
    print " <a href=\"/gallery/\">Home</a>";
 
    if($imageid <= $maximg) {
      $nsql     = "SELECT ID FROM MyGallery WHERE ID :;
      $nsql    .= "> '$imageid' ORDER BY ID ASC LIMIT 1";
      $next_row = mysql_fetch_assoc(mysql_query($nsql));
      $nid      = $next_row['ID'];
    }
 
    if($nid > 0){
      print " | <a href=\"/gallery/?id=$nid\">Next</a>";
    }
    print "</font>";
    print "</p>";
 
    # Display the image, and its description
    print "<img src=\"$ifilename\" alt=\"Gallery\" title=\"Gallery\">";
    print "<p align=\"center\">";
    print "$iinfo";
    print "</p>";
  }
?>
 
</body>
</html>

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 🙂

13 Replies to “PHP, MySQL & Pagination”

  1. @MarwaDi,
    This was hovering in my mind for a long time – next few should be similar too. Anyaayakkaagi kShame irali – idea was not to turn off/away a handful of my blog-readers…

  2. I want to say something about this site. This site deals with the binary numbers conversion. It deals with the binary numbers convert to the other numbers or characters. It is fully based on the computer software . It gives so much of information regarding the binaural beat secrets.
    binary numbers convert

  3. Hi, i read alot and i’m interrested on mysql optimization. This intrigues from your code:

    Witch is faster ?

    You normally use: SELECT field FROM tablename
    I normally use: SELECT count(field) FROM tablename

    From my testings it’s the same, i’ve tryed with small and medium databases, but i always looking for experience people to teach me. I wonder if you can help me ?

  4. @ |X-Crap|:
    For smaller databases, I believe it’s not that big of a deal. Since each step (selecting all records and then counting them) takes only few microseconds or less, it wouldn’t matter. My biggest database has only 60,000 entries and I still have no issues. Part of the decision depends on what you want to do with the query results. Like for example, if you want just the total number of rows to decide how many pages to create, I would use SELECT COUNT(field)… But when I want to do more with the query results, I usually use SELECT field…

    Which one do you use (a one step approach OR a two step approach) sometimes depends on the aesthetics – how good, elegant, small you want your code to be 🙂

  5. Hi, thanks for your response

    I only use for page pagination. I also noticed you do 2 query’s for next and previous field. I’ve done some research, after searching alot i found i way of doing in only one query:

    http://www.xaprb.com/journal/2006/04/28/how-to-find-next-and-previous-records-in-sql

    I haven’t tested to check if it’s faster than doing 3 single queries, but it’s a different interresting solution. About the count and select field variants, i’ve tryed in databases with 2 million records and it’s the same, so i guess i’ll stick with the count for pagination.

    Thank you for your response. 🙂

    Regards

  6. Hello,
    Any one can help me how come i keep getting error like this :
    Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING
    for the code block:

    do {
          printf("<a></a>", $idetails["ID"], $idetails['IFileName']);
        } while ($idetails = mysql_fetch_array($result1));
      }

    Thank you very much for help.

  7. I should say I have experienced this problem before and the solution that worked for me is given below:

    while ($idetails = mysql_fetch_array($result1)) {
      printf ("<a></a>",
               $idetails['ID'],
               $idetails['IFileName']);
    }

    But I do notice that you are not specifying (may be you did and the Comment Form didn’t display it) any ‘%s’ in your printf statement. A code-block with %s in the above case would look like:

    while ($idetails = mysql_fetch_array($result1)) {
      printf ("%s, %s",
               $idetails['ID'],
               $idetails['IFileName']);
    }

    The first %s gets replaced with the value of $idetails[‘ID’] and the second %s gets replaced with the value of $idetails[‘IFileName’].

    Hope this helps.

  8. I just have problem in this block. I always get this:Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING. for this line: alt=\”Thumbnails\”>“, $idetails[‘ID’], $idetails[‘IFileName’]); I tried many ways to solve it but no result. seeking help please. Thanks.

    do {
          printf("<a></a>", $idetails['ID'], $idetails['IFileName']);
        } while ($idetails = mysql_fetch_array($result1)) {
      printf ("%s, %s",
               $idetails['ID'],
               $idetails['IFileName']);
    }
  9. Sorry, I can not get this “<a></a>” displayed in last commends. but you know which part i am talking about. Sorry about that. Thanks

  10. @Will:

    I understand your frustration. Please send me the complete code that you are working on as an attachment in an email – I will take a look and try to solve the issue as soon as possible.

Comments are closed.