Making Query Result Pages

Except for limited queries on small databases, most SELECT statements will return tens, hundreds, or even thousands of records. When printing these records (to a Web page or wherever), you will presumably not want to display them all at one time. In such cases, you will need to make query result pages so that page one will display the first 25 records, page two the next 25, and so forth until all of the records have been displayed. This is called pagination.

Depending upon the programming language being used and the needs of the application, there are two logical ways of developing query result pages:

• Assign the query's resource link to a variable that gets passed from page to page (see the sidebar "Using a Resource Link").

• Reapply the query on each page, changing the start and end points (the LIMITs) accordingly.

For this example, I will use PHP, which must use the slightly more complex second method (as PHP closes a database connection when the script stops running). As my example, the script will browse through all of the records in a movies database by movie title. To start off, you'll need to create and populate the database.

Using a Resource Link Assuming that you are using a programming language that does not automatically close a database connection when a script runs (or that allows for permanent connections), you can simplify the steps outlined in this section. Here's how: Normally when you query a database, the result of that query can be assigned to a variable. The value of this variable is essentially a link to the database for that particular query. Normally data is retrieved within a loop, fetching a record through this link with each iteration. But if you can get the link to exist beyond the scope of a single script, it can be passed from page to page, allowing you to access rows as needed, without requerying the database every time. If you're creating an application, it could show X number of records, wait for the user to press a button, and then show the next X number. In such a case it's just a matter of using the same resource link, user input, and some loops.

Creating the database

This database will store information about movies and directors. For the sake of focusing on the task at hand (paginating query results), I'm going to implement a minimal version of this database. I'll just define two tables: one that stores directors and another that stores information about films. A one-to-many relationship will exist between the two, as each film has only one director (by the Director's Guild rules) and each director can make several films (Figure 12.12). If you'd like to expand upon this design, you could start by fleshing out these first two tables: adding biographic information about the director, production information about a film, and so on. Then you could create an actors table and an actors_films table that's an intermediary between actors and films (as there's a many-to-many relationship there).

To create the database:

CREATE DATABASE movies;

USE movies;

CREATE TABLE directors (
director_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(20),
last_name VARCHAR(30) NOT NULL,
PRIMARY KEY (director_id),
INDEX (last_name)
);

CREATE TABLE films (
film_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
director_id INT UNSIGNED NOT NULL,
title VARCHAR(100) NOT NULL,
year_released YEAR NOT NULL,
PRIMARY KEY (film_id),
INDEX (director_id),
INDEX (title),
INDEX (year_released)
);

INSERT INTO directors
(first_name, last_name)
VALUES
('Wes', 'Anderson'),
('Joel', 'Coen'),
('Paul Thomas', 'Anderson'),
('Zach', 'Braff'),
('Curtis', 'Hanson'),
('Marty', 'Scorsese'),
('Miranda', 'July'),
('Steven', 'Soderbergh');

INSERT INTO films
(director_id, file_title, year_released)
VALUES
(1, 'The Royal Tenenbaums', 2001),
(1, 'Bottle Rocket', 1996),
(1, 'Rushmore', 1998),
(2, 'Blood Simple', 1984),
(2, 'Raising Arizona', 1987),
(2, 'Miller\'s Crossing', 1990),
(2, 'Fargo', 1996),
(2, 'The Big Lebowski', 1998),
(2, 'O Brother, Where Art Thou?', 2000),
(2, 'The Man Who Wasn\'t There', 2001),
(2, 'Intolerable Cruelty', 2003),
(3, 'Boogie Nights', 1997),
(3, 'Magnolia', 2000),
(3, 'Punch-Drunk Love', 2002),
(4, 'Garden State', 2004),
(5, 'L.A. Confidential', 1997),
(5, 'Wonder Boys', 2000);

Paginating PHP pages

Now that you have a populated database, you can make the query results pages. As I mention in the introduction to this section, to achieve the pagination, I'll display X number of results on each page. To make sure that the right results are displayed, I use a LIMIT clause on my query.

For the first page, I should display the first X records, so my query would end with LIMIT X, or the more formal LIMIT 0, X (take X records starting from 0, which is the first record returned). For the second page, I've already seen the first X records, so I'll want to use LIMIT (X * 1), X. If you're displaying ten records per page, this means LIMIT 10, 10. In other words, start with the eleventh item (which is number 10 when you start counting from 0) and show the next ten. For the third page, you'd want LIMIT (X * 2), X. And so on.

In short, the LIMIT formula for pagination will be

LIMIT (X * (Y - 1)), X

where X is the number of records being displayed per page and Y is the page number. You have to subtract 1 so that the first page starts at 0 (X * (1 - 1)), the second at X (X * (2 - 1)), etc.

That's the hardest part. The only other tricks are:

• The first time the page is run, it must determine how many records are returned by the query.

• The page must pass back to itself all of the LIMIT and other values.

To create a query result page:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/ xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/ xhtml" xml:lang="en" lang="en">
<head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8" />
    <title>Browse the Movie Titles </title>
</head>
<body>
<?php

$display_number = 5;

[View full width]
$dbc = @mysqli_connect('localhost', 'username', 'password', 'movies') or die ('Could not
 connect to MySQL: ' . mysqli_connect_error() .'</body></html>');

[View full width]
if (isset($_GET['np'])) {
    $num_pages = (int) $_GET['np'];
} else {
    $q = 'SELECT COUNT(*) FROM directors, films WHERE directors. director_id=films
.director_id';
    $r = mysqli_query($dbc, $q);
    list($num_records) = mysqli_fetch_array ($r, MYSQLI_NUM);
    mysqli_free_result($r);
    if ($num_records > $display_ number) {
        $num_pages = ceil ($num_records/ $display_number);
    } else {
       $num_pages = 1;
    }
}

if (isset($_GET['s'])) {
    $start = (int) $_GET['s'];
} else {
    $start = 0;
}

[View full width]
$q = "SELECT CONCAT (directors.first_name, ' ', directors.last_name) AS Director, title AS
 Title FROM directors, films WHERE directors.director_id= films.director_id ORDER BY films
.title ASC LIMIT $start, $display_number";
$r = mysqli_query ($dbc, $q);
while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {
    echo "{$row['Director']} <i>{$row['Title']}</i><br />\n";
}

mysqli_free_result ($r);
mysqli_close($dbc);

if ($num_pages > 1) {
    echo '<hr width="50%" align="left" />';
    $current_page = ($start/ $display_number) + 1;

[View full width]
if ($current_page != 1) {
    echo '<a href= "browse_movies.php?s=' . ($start - $display_number) . '&np=' .
 $num_pages . '"> Previous</a> ';
}

[View full width]
for ($i = 1; $i <= $num_pages; $i++) {
    if ($i != $current_page) {
        echo '<a href= "browse_movies.php?s=' . (($display_number * ($i - 1))) . '&np=' . 
$num_pages . '">' . $i . '</a> ';
    } else {
       echo $i . ' ';
    }
}

[View full width]
if ($current_page != $num_pages) {
    echo '<a href= "browse_movies.php?s=' . ($start + $display_number) . '&np=' .
 $num_pages . '">Next </a> ';
}

}
?>
</body>
</html>