Pagination

from the Artful MySQL Tips List

Paginating a MySQL query result in PHP is just a matter of connecting to the database server, then looping through the following steps each time the user asks for more data:

if it's the first instance of the loop, set offset=0, otherwise adjust the offset value according to whether the user clicked the Top, Previous, Next or Bottom button,

issue the query with a LIMIT clause incorporating that offset,

retrieve column headers and paint them,

retrieve the query result and paint the data rows,

configure and paint Top, Previous, Next and Bottom command buttons according to the current offset, and when the user clicks on one, POST back to the page the current offset value and the identity of the clicked button.

The following script does that for any table of any size. Beware, though, that with more than a few hundred thousand rows, retrieval can be slow: