PHP DevCenter
oreilly.comSafari Books Online.Conferences.

advertisement


Creating Dynamic Next / Previous Buttons with PHP and MySQL

by Joao Prado Maia
11/02/2000

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:

<?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);
mysql_free_result($result);
?>

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:

<?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.

  • Generic enough that it would work with scripts of several purposes.
  • Independent from HTML code and layout.
  • Customizable to work with multipurpose scripts
  • Able to automatically remember conditionals on SQL queries
  • Compatible with both GET and POST form methods

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.

Pages: 1, 2

Next Pagearrow




Valuable Online Certification Training

Online Certification for Your Career
Earn a Certificate for Professional Development from the University of Illinois Office of Continuing Education upon completion of each online certificate program.

PHP/SQL Programming Certificate — The PHP/SQL Programming Certificate series is comprised of four courses covering beginning to advanced PHP programming, beginning to advanced database programming using the SQL language, database theory, and integrated Web 2.0 programming using PHP and SQL on the Unix/Linux mySQL platform.

Enroll today!


Sponsored by: