MySQL Cookbook

Generating Previous-Page and Next-Page Links

Problem

A query matches so many records that displaying them all in a single web page produces an unwieldy result.

Solution

Split the query output across several pages and include links that allow the user to navigate among pages.

Discussion

If a query matches a large number of records, showing them all in a single web page can result in a display that’s difficult to navigate. For such cases, it can be more convenient for the user if you split the result among multiple pages. Such a paged display avoids overwhelming the user with too much information, but is more difficult to implement than a single-page display.

A paged display typically is used in a search context to present records that match the search parameters supplied by the user. To simplify things, the examples in this section don’t have any search interface. Instead, they implement a paged display that presents 10 rows at a time from the result of a fixed query:

SELECT name, abbrev, statehood, pop  FROM states ORDER BY name;

MySQL makes it easy to select just a portion of a result set: add a LIMIT clause that indicates which records you want. The two-argument form of LIMIT takes values indicating how many records to skip at the beginning of the result set, and how many to select. The query to select a section of the states table thus becomes:

SELECT name, abbrev, statehood, pop  FROM states ORDER BY name
LIMIT skip,select;

One issue, then, is to determine the proper values of skip ...