Showing every record on one page is slow and hard to use. Pagination splits a list into pages, such as 10 products per page.
The idea
Two numbers decide what to show:
$perPage: how many rows per page.$page: the current page (1, 2, 3 …).
The database needs LIMIT $perPage OFFSET $offset, where:
offset = (page - 1) * perPage| Page | Offset | Rows shown (10 per page) |
|---|---|---|
| 1 | 0 | 1 to 10 |
| 2 | 10 | 11 to 20 |
| 3 | 20 | 21 to 30 |
A pagination helper
<?php
function paginate(int $total, int $page, int $perPage): array
{
$pages = max(1, (int) ceil($total / $perPage));
$page = min(max(1, $page), $pages);
return [
"page" => $page,
"pages" => $pages,
"offset" => ($page - 1) * $perPage,
"perPage" => $perPage,
"hasPrev" => $page > 1,
"hasNext" => $page < $pages,
];
}
print_r(paginate(47, 3, 10));Array
(
[page] => 3
[pages] => 5
[offset] => 20
[perPage] => 10
[hasPrev] => 1
[hasNext] => 1
)The function also clamps the page number: a visitor who asks for page 999 gets the last page, and page -4 gets page 1.
<?php
function paginate(int $total, int $page, int $perPage): array
{
$pages = max(1, (int) ceil($total / $perPage));
$page = min(max(1, $page), $pages);
return ["page" => $page, "pages" => $pages, "offset" => ($page - 1) * $perPage];
}
echo paginate(47, 999, 10)["page"] . " " . paginate(47, -4, 10)["page"] . " " . paginate(0, 1, 10)["pages"];5 1 1Query one page
<?php
function paginate(int $total, int $page, int $perPage): array
{
$pages = max(1, (int) ceil($total / $perPage));
$page = min(max(1, $page), $pages);
return ["page" => $page, "pages" => $pages, "offset" => ($page - 1) * $perPage];
}
$pdo = require __DIR__ . "/db.php";
$perPage = 2;
$total = (int) $pdo->query("SELECT COUNT(*) FROM products")->fetchColumn();
$p = paginate($total, (int) ($_GET["page"] ?? 2), $perPage);
$statement = $pdo->prepare("SELECT name FROM products ORDER BY id LIMIT :limit OFFSET :offset");
$statement->bindValue(":limit", $perPage, PDO::PARAM_INT);
$statement->bindValue(":offset", $p["offset"], PDO::PARAM_INT);
$statement->execute();
echo "Page {$p['page']} of {$p['pages']}: ";
echo implode(", ", $statement->fetchAll(PDO::FETCH_COLUMN));Page 2 of 3: Bag, MarkerBind LIMIT and OFFSET as integers (PDO::PARAM_INT).
Always use ORDER BY
Without ORDER BY, the database may return rows in any order, so pages could repeat or skip rows. Use a stable order, ideally ending with a unique column such as id.
The page links
<nav aria-label="Pagination">
<?php if ($p["hasPrev"]): ?>
<a href="?<?= h(http_build_query([...$_GET, "page" => $p["page"] - 1])) ?>">Previous</a>
<?php endif; ?>
Page <?= $p["page"] ?> of <?= $p["pages"] ?>
<?php if ($p["hasNext"]): ?>
<a href="?<?= h(http_build_query([...$_GET, "page" => $p["page"] + 1])) ?>">Next</a>
<?php endif; ?>
</nav>[...$_GET, "page" => 3] keeps the other query values (search, filter, sort) when you change the page. http_build_query() encodes them safely.
A performance note
OFFSET with a big number is slow, because the database still counts through all the skipped rows. For huge tables, keyset pagination (WHERE id > :last_id ORDER BY id LIMIT 10) is faster. For most sites, LIMIT/OFFSET is fine.