Published on (
 See this if you're having trouble printing code examples

Creating Dynamic Next / Previous Buttons with PHP and MySQL

by Joao Prado Maia

Coming from a previous ASP/MS SQL job experience (I know, I know), the idea of creating dynamic navigation links using PHP and MySQL or PostgreSQL seemed to be an easy task. I used to use ADO to do these dynamic "next," "previous," and page numbering links, and each time a user would click on a link, ADO would remember the page numbering. It was pretty easy to maintain and also fast to code.

There are several articles that talk about how to create Next/Previous buttons for query results, but it is often dirty code, with 30 or more lines to do the same thing. On my last job, I used a class to create these same Next/Previous buttons, but the code was not very clean or efficient.

I needed a clean class to use on my personal site, but I wanted something that would not require 20 lines of code for each page that needed Next/Previous buttons. I also wanted something that could be used in several ways and would show either all the links to specific pages or just the Next/Previous links. I then decided to create a new class and do it the Right Way (tm) this time.

The result of my five days (part-time after work) of work on this class resulted in beautiful code that I really felt the need to show off! Seriously though, the class is pretty much perfect for big projects that need to create Next/Previous buttons in different scripts. It is also totally customizable, works with both MySQL and PostgreSQL, and is very handy for quickly building complex scripts.

This article will show you how to use this class to handle your navigational link needs. You can re-use it in any way you want, as long as you preserve the credits.

Understanding the problem

To fully understand why a PHP class would be needed for this task, we should look at how this same task would be done without it. I will refer to databases as MySQL databases, so please bear with me, PostgreSQL fans.

Take this as an example: We have 200 records, and we want to show 30 records at a time on our screens. This total of 200 records can grow with time, so we need to think dynamically here. I want to have "Next" and "Previous" links and also links to specific pages, like "1," "2," or whatever.

For that, we need to determine the total number of records in our MySQL table. We can then get the number of pages by dividing the total number of records by the number of records we want to display on each screen, or 30. This is fairly easy to accomplish; just select everything from the table, get the number of rows returned by the query, and, there you go, you have the total and can calculate the number of pages.

Here's an example using the first part of example.php:

// database connection stuff here
$rows_per_page = 30;
$sql = "SELECT * FROM table_name";
$result = mysql_query($sql, $db);
$total_records = mysql_num_rows($result);
$pages = ceil($total_records / $rows_per_page);

By the way, I had to use the function ceil() in the above example since the number of pages was wrong. If I had 100 records, and divided by 30 as in the example, the number of pages would be 3.33333. However, we need 4 pages to show all the records: 30 on the first, second and third, and 10 on the fourth one. So we always need to "round up" the number of pages.

Unleashing the monster

Well, that was easy. Now we need to get the "limited" version of our query. Instead of getting all the rows back from the database, we only want 30 each time. MySQL and PostgreSQL both have a handy SQL function for this: LIMIT. They disagree on the order of the parameters, but they both accomplish the same task.

MySQL's LIMIT syntax:

SELECT * FROM table_name LIMIT 0, 30

PostgreSQL's LIMIT syntax:

SELECT * FROM table_name LIMIT 30, 0

These SQL queries both mean "give me all columns from table table_name, but only 30 records, starting at record number 0." MySQL treats the first parameter as the number of the starting row, and PostgreSQL treats it as the number of rows to return. It is pretty obvious now, but sometimes even I get confused on this.

We need to create screens showing a limited number of the returned rows using PHP and our handy LIMIT SQL function. We will use it to display the rows returned for each page, so the first page or screen of our script will show from record 0 to 29, the second screen from 30 to 59, and so on.

Here's the continuation of the contents of example.php:

// database connection stuff here
if (!isset($screen))
  $screen = 0;
$start = $screen * $rows_per_page;
$sql = "SELECT description FROM table_name ";
$sql .= "LIMIT $start, $rows_per_page";
$result = mysql_query($sql, $db);
$rows = mysql_num_rows($result);
for ($i = 0; $i < $rows; $i++) {
  $description = mysql_result($result, $i, 0);
  echo "\$description = $description<br>\n";
echo "<p><hr></p>\n";
// let's create the dynamic links now
if ($screen > 0) {
  $url = "example.php?screen=" . $screen - 1;
  echo "<a href=\"$url\">Previous</a>\n";
// page numbering links now
for ($i = 0; $i < $pages; $i++) {
  $url = "example.php?screen=" . $i;
  echo " | <a href=\"$url\">$i</a> | ";
if ($screen < $pages) {
  $url = "example.php?screen=" . $screen + 1;
  echo "<a href=\"$url\">Next</a>\n";

Isn't this ugly? The example I showed is the easiest one to do, and even so it took us 30 lines of code to finish. Usually you would need to do conditional queries, to show only records that have something in the "description" column, for instance. However, to do that you would need to specify the conditionals on your url, so the conditional could be remembered on the next screen.

I don't even need to point out that these 30 lines are very non-portable, so if you ever needed to use the same code on a different script, you would have to change most of the 30 lines. All of this doesn't help make the code maintainable, either.

There is a better way

Okay, so we now know how to not code a navigation link script. Now let's try to determine what a good class/function should be like.

While designing my PHP class, I ran into a problem that I hadn't thought of at first: how to make the script always "remember" the conditionals of the queries. That means, if I searched all the rows for the string "PHP" in the description column, the class should remember this decision.

I thought about using the global variables $HTTP_GET_VARS and $HTTP_POST_VARS, but these variables are only available if the track_vars directive is enabled in the global configuration file. So I began coding, parsing, and building "GET" type URLs from the Apache environment variable $QUERY_STRING -- and I completely forgot that the user might want to use the class on a form using the POST method.

While it was a very cool thing to do, building a class that could work on any type of configuration (since it didn't need the $HTTP_*_VARS), the script now would not work with forms that used the POST method, as the $QUERY_STRING variable holds all the GET-type variables passed to a script. It was clearly going to be a problem if the class only worked that way.

I decided to change the class back to the $HTTP_*_VARS method, and everything went fine after that. It is the cleanest way to do the job. Anyway, let's keep with it.

The magic code arrives

Now that we understand what a good navigation class needs to have, let's start coding it. Some variables are going to need to be available so the user can choose how many records to show in each screen, for instance. The user also needs to be able to change the default values of the "Next page" and "Previous page" strings to whatever they want. They can also choose whether to show the "Next page" and "Previous page" strings even when they are not needed, i.e., on the first and last screens of your script.

The variables are:

  var $numrowsperpage = 10;
  var $str_previous = "Previous page";
  var $str_next = "Next page";

  // These two variables are used internally 
  // by the class' functions
  var $file;
  var $total_records;

I split the class into three functions to make the code easier to maintain and read in the long run. It might not be very intuitive at first, but after a couple of minutes of studying the code, you will recognize its simplicity. Thank you, by the way ;-)

Let's start with the execute() function :

  function execute($sql, $db, $type = "mysql") {
    // global variables needed by the function
    global $total_records, $row, $numtoshow;

    // number of records to show at a time
    $numtoshow = $this->numrowsperpage;
    // $row is actually the number of the row of 
    // records (the page number)
    if (!isset($row)) $row = 0;
    // the record start number for the SQL query
    $start = $row * $numtoshow;
    // check the database type
    if ($type == "mysql") {
      $result = mysql_query($sql, $db);
      $total_records = mysql_num_rows($result);
      $sql .= " LIMIT $start, $numtoshow";
      $result = mysql_query($sql, $db);
    } elseif ($type == "pgsql") {
      $result = pg_Exec($db, $sql);
      $total_records = pg_NumRows($result);
      $sql .= " LIMIT $numtoshow, $start";
      $result = pg_Exec($db, $sql);
    // returns the result set so the user 
    // can handle the data
    return $result;

As you can note, this function executes the SQL query on the selected database. The default type of database is MySQL, but that can be changed as well. It first runs the full query, that is, the query returning all the rows, so we can always know the total number of records, and the current offset for the pages.

Building the navigation link array

Since we already have executed our query, the navigation links can be created. This is an especially cool feature, since instead of printing out all the links in some pre-defined format, the class is actually returning an array with all the links (i.e., $array[0] holds <A HREF='example.php?row=0'>1</A>). This enables users to print the navigation links in whatever fashion they choose, adding even more customizability to the class.

Let's look at the contents of the getlinks() function -- you can view it here.

What it does is calculate the total number of pages that can be built according to the number of records per page the user selected. Then, it starts creating an array of links to return to the calling script. This is especially important, since a lot of user-defined options are available in this stage: whether to show page numbering links or not, whether to show the "Next" and "Previous" links or not, and whether to show the "Next" and "Previous" strings when those links are not needed.

The first element of this array is always the "Previous" link/string. This means that $array[0] is always <A HREF='example.php?row=X'>Previous</A>, the string "Previous" or just blank. The last element of the array is the "Next" link/string, with the same possibilities as the first one. Finally, all the elements between the first and the last elements are the page numbering links/strings.

Since the class doesn't affect how the script outputs the navigation link information in any way, the user is always free to customize the returned array to the page design.

More on building the magic array

Okay, by this point you may be a little bit confused, or even overwhelmed by so much information at once. The class can seem complex at first, but let's keep on analyzing it.

The second assignment of the getlinks() function is to actually build the GET type URL that is going to be returned to the calling script. It is completely dynamic, so the options that the user chose in the previous script are remembered on the next screen.

View contents of build_geturl() function here.

All this function does is get the filename of the current script, or the requested URL and assign it to a class function for later use. It then checks for the form method type and builds a GET-type URL to be appended later to the navigation link's URL. That's how the class always knows and remembers the URL and variables passed to it. Sweet, huh? :)

You can download an example of how to use this class, and of course, the class itself from these links -- download them as text files, and then rename them so they have a .php extension: example.txt and navbar.txt.

That was it. You can use this class on your own projects as much as you want, but please leave the credits on it. Also, if you want, send me an e-mail saying that you liked it and you are using it for your projects. It will make me happy.

Joao Prado Maia is a web developer living in Houston with more than four years of experience developing web-based applications and loves learning new technologies and programming languages.

Discuss this article in the O'Reilly Network PHP Forum.

Return to the PHP DevCenter.


Copyright © 2009 O'Reilly Media, Inc.