Pagination

A table may hold thousands of records, but a page shows ten or twenty. The rest is reachable through the "2", "3", "Next" links. For that you request not everything from the database but one portion.

The examples use an in-memory SQLite database and do not run here — run them on your machine.

LIMIT and OFFSET

LIMIT sets how many rows to return, OFFSET how many to skip:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT)');
 
for ($i = 1; $i <= 25; $i++) {
    $pdo->exec("INSERT INTO posts (title) VALUES ('Post $i')");
}
 
$titles = $pdo->query('SELECT title FROM posts ORDER BY id LIMIT 3 OFFSET 0')
    ->fetchAll(PDO::FETCH_COLUMN);
 
echo implode(', ', $titles); // Post 1, Post 2, Post 3

The offset from a page number

To get the right page, the offset is calculated by a formula: the page number minus one, multiplied by the page size:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT)');
 
for ($i = 1; $i <= 25; $i++) {
    $pdo->exec("INSERT INTO posts (title) VALUES ('Post $i')");
}
 
$page = 3;
$perPage = 10;
$offset = ($page - 1) * $perPage;
 
$statement = $pdo->prepare('SELECT title FROM posts ORDER BY id LIMIT :limit OFFSET :offset');
$statement->bindValue('limit', $perPage, PDO::PARAM_INT);
$statement->bindValue('offset', $offset, PDO::PARAM_INT);
$statement->execute();
 
echo implode(', ', $statement->fetchAll(PDO::FETCH_COLUMN));

The third page holds posts 21 to 25.

How many pages in total

The total number of records is divided by the page size and rounded up:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT)');
 
for ($i = 1; $i <= 25; $i++) {
    $pdo->exec("INSERT INTO posts (title) VALUES ('Post $i')");
}
 
$total = (int) $pdo->query('SELECT COUNT(*) FROM posts')->fetchColumn();
$pages = (int) ceil($total / 10);
 
echo "Records: $total, pages: $pages"; // Records: 25, pages: 3

The page number from the address

The number arrives in the address: /posts.php?page=2. It is cast to a number and limited:

<?php
// posts.php
 
$page = max(1, (int) ($_GET['page'] ?? 1));
$page = min($page, $pages);

Now page=abc, page=-5 and page=9999 do not break the query.

Links

<?php
 
function pageLinks(int $current, int $pages): string
{
    $links = [];
 
    for ($page = 1; $page <= $pages; $page++) {
        $links[] = $page === $current
            ? "<b>$page</b>"
            : "<a href=\"?page=$page\">$page</a>";
    }
 
    return implode(' ', $links);
}
 
echo pageLinks(2, 3);

The current page is printed in bold, the others as links.

Large tables

With a large offset the database still reads the skipped rows: OFFSET 100000 is slow. Long feeds use another technique — remember the identifier of the last record shown and ask for the ones after it:

SELECT id, title FROM posts WHERE id > 120 ORDER BY id LIMIT 10;

That is how a "Show more" button works.

Common mistake

In MySQL, values passed through execute([$perPage, $offset]) are inserted as strings, and the query LIMIT '10' ends with an error. For LIMIT and OFFSET use bindValue() with PDO::PARAM_INT. And always specify ORDER BY: without ordering the row order is undefined, and a record may land on two pages or on none.

Thanks for reading!