PHP MySQL Pagination: LIMIT OFFSET, Total Counts

Almost every list page needs pagination. The SQL side is explained in LIMIT and OFFSET and Pagination Patterns. This lesson puts it together in PHP, safely.

Offset pagination

index.php

PHP

<?php
$pdo = require __DIR__ . '/db.php';

$_GET['page'] = '2';                                          // simulated request: ?page=2

$perPage = 3;
$page = max(1, (int) ($_GET['page'] ?? 1));                   // never trust the page number
$total = (int) $pdo->query('SELECT COUNT(*) FROM products')->fetchColumn();
$pages = max(1, (int) ceil($total / $perPage));
$page = min($page, $pages);

$stmt = $pdo->prepare('SELECT id, name FROM products ORDER BY id LIMIT :limit OFFSET :offset');
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', ($page - 1) * $perPage, PDO::PARAM_INT);
$stmt->execute();

echo "Page $page of $pages ($total products)\n";
foreach ($stmt as $row) {
    echo $row['id'], ' ', $row['name'], "\n";
}

Output

OUTPUT

Page 2 of 3 (8 products)
4 Marker
5 Diary
6 Stapler

The important details:

(int) and max(1, ...) turn any input (abc, -5, 99999999999) into a valid page number.
LIMIT and OFFSET are bound with PARAM_INT (Prepared Statements).
ORDER BY has a unique tie-breaker. Without a deterministic order, rows can repeat or vanish between pages. If you sort by price, use ORDER BY price, id.

Page links

index.php

PHP

<?php
$pages = 4;
$page = 2;
$links = [];
for ($p = 1; $p <= $pages; $p++) {
    $links[] = $p === $page ? "[$p]" : (string) $p;
}
echo ($page > 1 ? '< prev  ' : ''), implode(' ', $links), ($page < $pages ? '  next >' : ''), "\n";

Output

OUTPUT

< prev  1 [2] 3 4  next >

Build real links with href="?page=N" and escape every value with htmlspecialchars() (PHP Escaping Output).

The cost of a large offset

OFFSET 100000 does not “jump”. MySQL reads and throws away the first 100 000 rows, so deeper pages are slower. A real measurement shape (EXPLAIN ANALYZE on a million-row table) is shown in Pagination Patterns. If your users go only a few pages deep, offsets are fine. For long lists, feeds and APIs, use keyset pagination.

Keyset (seek) pagination

Instead of “skip N rows”, say “continue after the last row I showed“:

index.php

PHP

<?php
$pdo = require __DIR__ . '/db.php';

$perPage = 3;
$afterId = (int) ($_GET['after'] ?? 0);               // the last id of the previous page (0 = first page)

$stmt = $pdo->prepare('SELECT id, name FROM products WHERE id > :after ORDER BY id LIMIT :limit');
$stmt->bindValue(':after', $afterId, PDO::PARAM_INT);
$stmt->bindValue(':limit', $perPage + 1, PDO::PARAM_INT);          // one extra row tells us if there is a next page
$stmt->execute();
$rows = $stmt->fetchAll();

$hasNext = count($rows) > $perPage;
$rows = array_slice($rows, 0, $perPage);
foreach ($rows as $row) {
    echo $row['id'], ' ', $row['name'], "\n";
}
echo $hasNext ? 'next: ?after=' . end($rows)['id'] : 'last page', "\n";

Output

OUTPUT

1 Notebook
2 Pen
3 Backpack
next: ?after=3

Because WHERE id > :after uses the primary key index, every page is just as fast as the first. Fetching perPage + 1 rows tells you whether another page exists, without a COUNT(*).

When you sort by a column that is not unique (for example price), the cursor must contain both values, and the condition becomes a row comparison:

index.php

PHP

<?php
$pdo = require __DIR__ . '/db.php';

$lastPrice = '45.00';     // from the previous page's last row
$lastId = 4;
$stmt = $pdo->prepare('SELECT id, name, price FROM products WHERE (price, id) > (:price, :id) ORDER BY price, id LIMIT 3');
$stmt->execute(['price' => $lastPrice, 'id' => $lastId]);
foreach ($stmt as $row) {
    echo $row['price'], ' ', $row['name'], "\n";
}

Output

OUTPUT

1
2
3

80.00 Notebook
95.50 Stapler
150.00 Diary

(price, id) > (45.00, 4) compares rows the same way as sorting. Add an index on (price, id) so MySQL can seek straight to the right place (Indexes).

	Offset	Keyset
Jump to page 50 directly	Yes	No (only next/previous)
Deep pages	Slower and slower	Constant speed
Rows inserted while browsing	Rows may repeat or be skipped	Stable
Needs	`ORDER BY`	A unique, indexed sort key
Good for	Admin tables, small lists	Feeds, infinite scroll, APIs, exports

Counting the total

SELECT COUNT() on a big InnoDB table scans an index and can be slow, and it runs on every page view. Options: cache the count for a minute, show “more than 10 000” using SELECT COUNT() FROM (SELECT 1 FROM t WHERE ... LIMIT 10001) x, or use keyset pagination with “next” and “previous” only. Do not use SQL_CALC_FOUND_ROWS: it is deprecated since MySQL 8.0.17 and removed in later versions’ plans.

Filters and pagination together

Add the WHERE conditions with placeholders, and use the same conditions for the count query, so the totals match:

index.php

PHP

<?php
$pdo = require __DIR__ . '/db.php';

$category = 'writing';
$count = $pdo->prepare('SELECT COUNT(*) FROM products WHERE category = ?');
$count->execute([$category]);
$total = (int) $count->fetchColumn();

$stmt = $pdo->prepare('SELECT name FROM products WHERE category = :c ORDER BY name LIMIT :limit OFFSET :offset');
$stmt->bindValue(':c', $category);
$stmt->bindValue(':limit', 2, PDO::PARAM_INT);
$stmt->bindValue(':offset', 2, PDO::PARAM_INT);
$stmt->execute();
echo "$total in $category; page 2 shows: ", implode(', ', $stmt->fetchAll(PDO::FETCH_COLUMN)), "\n";

Output

OUTPUT

3 in writing; page 2 shows: Pencil

lightbulb

Cap the page size

Accept per_page from the URL only within a range (for example 10 to 100). An unlimited page size is a denial-of-service switch.