JavaScript Editor Js editor     Website development 



Main Page

Previous Page
Next Page

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

Figure 12.12. The simple design of this database.


To create the database:

1.
Log in to the mysql client as a user capable of creating new databases.

2.
Create and select a movies database.

CREATE DATABASE movies;

USE movies;

Instead of using an existing database, I'll be making a new one for storing movie information. The database being used in this example has not previously been designed or implemented. If you followed the steps in Chapter 2, "Running MySQL," you may have created a movies database, but without any tables (when creating users). If you've already created this database, you can skip the first SQL command and just use the second to select it.

3.
Create a new table called directors (Figure 12.13).

Figure 12.13. Creating a table to store information about directors.


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)
);

This is the bare-bones directors table. It just stores each director's first and last names. I've put an index on the last name, as that is likely to be used in queries.

4.
Create the second table (Figure 12.14).

Figure 12.14. Creating a table to store information about films.


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)
);

The films table has a little more to it, but not much. I index the director_id field, as it will be used in joins. I also index the film's title and year of release, as these are likely to be used in WHERE or ORDER BY clauses.

5.
Populate the directors table (Figure 12.15).

Figure 12.15. A few records are added to the directors table.


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');

This is just some sample data I'm using. Fill in your own favorites as you see fit.

6.
Populate the films table (Figure 12.16).

Figure 12.16. Many records are added to the films table.


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);

Each of these films corresponds to one of the directors that have already been added.

7.
Repeat Steps 5 and 6 until the directors and films tables are well populated.

In order to adequately demonstrate the concept of pagination, you'll need quite a lot of records to retrieve.

Tip

  • All of this SQL is available for download from the book's corresponding Web site.


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:

1.
Begin a new PHP script in your text editor (Script 12.3).

<!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

Script 12.3. This PHP script demonstrates how easily you can paginate your query result pages.

1     <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
2             "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
3     <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
4     <head>
5         <meta http-equiv="content-type" content="text/html; charset=utf-8" />
6         <title>Browse the Movie Titles</title>
7     </head>
8     <body>
9     <?php
10
11    // ***** browse_movies.php *****
12    // ***** Script 12.3 *****
13    // This script generates query result pages
14    // of movie titles in the movies database.
15
16    // Number of records to show per page:
17    $display_number = 5;
18
19    // Connect to MySQL:
20    $dbc = @mysqli_connect('localhost', 'username', 'password', 'movies') or die ('Could
 not connect
      to MySQL: ' . mysqli_connect_error() . '</body></html>');
21
22    // Determine how many records there are:
23    if (isset($_GET['np'])) {
24
25     $num_pages = (int) $_GET['np'];
26
27  } else {
28
29     // Find out how many records there are.
30     $q = 'SELECT COUNT(*) FROM directors, films WHERE directors.director_id=films
.director_id';
31
32     // Get the number.
33     $r = mysqli_query($dbc, $q);
34     list($num_records) = mysqli_fetch_array($r, MYSQLI_NUM);
35     mysqli_free_result($r);
36
37     // Calculate the number of pages:
38     if ($num_records > $display_number) {
39         $num_pages = ceil ($num_records/$display_number);
40     } else {
41         $num_pages = 1;
42    }
43
44  }
45
46  // Determine where in the database to start returning results:
47  if (isset($_GET['s'])) {
48      $start = (int) $_GET['s'];
49  } else {
50      $start = 0;
51  }
52
53  // Define the query:
54  $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";
55
56  // Run the query:
57  $r = mysqli_query ($dbc, $q);
58
59  // Display all of the records:
60  while ($row = mysqli_fetch_array ($r, MYSQLI_ASSOC)) {
61      echo "{$row['Director']} <i>{$row['Title']}</i><br />\n";
62  }
63
64  // Clean up:
65  mysqli_free_result ($r);
66  mysqli_close($dbc);
67
68  // Make the links to other pages, if necessary:
69  if ($num_pages > 1) {
70
71      echo '<hr width="50%" align="left" />';
72
73      // Determine what page the script is on:
74      $current_page = ($start/$display_number) + 1;
75
76      // If it's not the first page, make a Previous button:
77      if ($current_page != 1) {
78          echo '<a href="browse_movies.php?s=' . ($start - $display_number) . '&np=' . 
$num_pages
            . '">Previous</a> ';
79      }
80
81      // Make all the numbered pages:
82      for ($i = 1; $i <= $num_pages; $i++) {
83
84        // Don't link the current page:
85        if ($i != $current_page) {
86            echo '<a href="browse_movies.php?s=' . (($display_number * ($i - 1))) . '&np=' .
              $num_pages . '">' . $i . '</a> ';
87        } else {
88            echo $i . ' ';
89        }
90
91     }
92
93     // If it's not the last page, make a Next button:
94     if ($current_page != $num_pages) {
95         echo '<a href="browse_movies.php?s=
          ' . ($start + $display_number) . '&np=' . $num_pages . '">Next</a> ';
96     }
97
98    }
99
100  ?>
101  </body>
102  </html>

2.
Establish the number of records to show per page.

$display_number = 5;

For sake of convenience, I prefer to set this number as a variable. This way, I can change how the script works by changing only one value (even though the number will be used several times over the course of the script).

Since my database isn't that full yet, I'm using a small number.

3.
Connect to the database.

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

As always, you could place this information in a separate configuration file for added security.

4.
Determine how many pages will need to be used to display all of the records.

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;
    }
}

This is the first step in generating query result pages: seeing how many records will be displayed and therefore how many pages will be needed.

If the URL has an np value (for the number of pages), this means that the number has already been calculated and does not need to be determined again. If np does not have a value, it will need to be derived.

To calculate the number of pages, a modified version of the query is run to count how many records will be returned. If there are more rows than will fit on one page, I divide the number of rows by the number of rows to display per page and round this value up to the next higher integer. Otherwise, one page will suffice.

5.
Determine with what record the query should start displaying.

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

As I explain in the introduction to these steps, the query itself will use a LIMIT clause to show up to X records per page, starting from X * (Y - 1). For this reason I need to determine whether the LIMIT should start at 0 (which is the first record) or elsewhere. If the s value (for start), appended to the URL, has a value, the query will use it. Otherwise, it will begin at 0.

6.
Display all of the records for this particular page.

$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";
}

The query in this step returns the actual records and uses a LIMIT clause. Aside from the query itself, this section of the script merely displays each record in a basic format (Figure 12.17).

Figure 12.17. Each movie's director and title are displayed by the page.


7.
Clean up the database resources.

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

8.
Begin making hyperlinks to the other query result pages.

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

If the query requires multiple pages, I'll need to make links so that the user can browse through them all. In this example, the navigation will have a previous link, a next link, and numbered links for every page (with the current page unlinked, Figure 12.18).

Figure 12.18. The links to the paginated pages.


To calculate the current page, I add 1 to the division of the start number divided by the display number. For example, the first page will have a $start of 0 and a $display_number of 5. Zero divided by 5 is zero, plus 1 is 1. Hence, it's the first page. The fourth page will have a $start value of 15 (because it's showing the fourth group of five records from the database) and a $display_number of 5 still.

9.
Create the Previous link.

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

If it is not the first page, I want to make a link to the previous page. The link is a URL of the format browse_movies.php?s= A&np=B, where s is the number to start with and np is the number of total pages. The A value will be based upon the current start value minus the number of records to display per page (in other words, the previous page should begin $display_number of records prior to this one). The B value will be based upon the calculated number of pages.

10.
Generate the numbered links.

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 . ' ';
    }
}

So that the user can immediately jump through the list of movies, I want to make numbered links. To do so, I loop through the numbers 1 to the total number of pages, creating a link for each number except for the current page (no reason to link a page to itself). The s value here is calculated as $i minus 1, multiplied by the number of records displayed per page. For the second page, $i minus 1 is 1, times 5 is 5, meaning that the second page should begin with the second group of five records out of the database.

11.
Make the Next page link.

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

12.
Close the links conditional and complete the HTML page.

}
?>
</body>
</html>

13.
Save the file as browse_movies.php, upload it to your Web server, and test in a Web browser (Figures 12.19, 12.20, and 12.21).

Figure 12.19. Upon first viewing browse_movies.php, the first five movies in the database (alphabetical by title) are displayed, along with links to the other pages.


Figure 12.20. Clicking any numbered link brings the user to that page.


Figure 12.21. The last page will not have a Next link, just as the first does not have a Previous link (Figure 12.19).


Tips

  • One potential flaw in this system is that if the database changes between the different executions of the query, it could throw off the result pages.

  • In order for this process to work, the two queriesthe one that returns the number of records and the one that actually retrieves the recordsmust have comparable results. Always test both queries to ensure this is the case.

  • If your query uses any other variables, such as a search term, they must also be passed from page to page (in the URL) or else the paginated pages will not work.



Previous Page
Next Page


JavaScript Editor Js editor     Website development


©