O'Reilly logo

MySQL Cookbook by Paul DuBois

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required