Easy Pagination

While writing a model I needed an easy way to set the starting record based only upon the page number that we’re on. This has always vexed me for some reason, but then it just hit me. The page number can be used to mathematically compute the starting record if we know the number of records to be displayed on each page (and we usually do).

So if you were planning on displaying a fixed number of records per page and you knew what page of the view you were showing of the set, you would compute your page number as follows:

(page number – 1) * number of records

Solving for page 1 and displaying 10 records at a time:

(1 - 1) = 0 * 10 = 0

You would use a SQL statement that looks something like

SELECT * FROM `my_table` WHERE 1 LIMIT 0, 10

Solving for page 3 and displaying 10 records at a time:

(3 - 1) = 2 * 10 = 20

SELECT * FROM `my_table` WHERE 1 LIMIT 20, 10

In PHP, it would look something like this:

// The number of records to show per page.
$displayRecordCount = 10;

// If a form submission includes a page use it, otherwise default to page 1.
$pageNo = isset($_REQUEST['page']) ? $_REQUEST['page'] : 1;

// Don't forget order of operations -- the parenthesis are significant here!
$start = ( $pageNo - 1 ) * $displayRecordCount;

// Assuming we're connected to MySQL via PDO in variable $dbh...
$sth = $dbh->prepare(sprintf(
    "SELECT * FROM `my_table` WHERE 1 LIMIT %d, %d",
    $start,
    $displayRecordCount
));
$sth->execute();

// $rows will contain the paginated records to display.
$rows = $sth->fetchAll();

So using only a single line of code, I am able to find my starting point in my database based on the page the user is looking to view at any given time. I just plug that into my model and off we go!