MySQL Cookbook, 2nd Edition

Generating Previous-Page and Next-Page Links

Problem

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

Solution

Split the statement output across several pages and include links that enable the user to navigate among pages.

Discussion

If a statement matches a large number of rows, 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 rows that match the search parameters supplied by the user. To simplify things, the examples in this recipe 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 statement:

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 rows you want. The two-argument form of LIMIT takes values indicating how many rows to skip at the beginning of the result set, and how many to select. The statement to select a section of the states table thus becomes:

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

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