PHP MySQL pagination is one of those things every web app needs sooner or later. Once your result set gets even moderately large, dumping everything onto one page stops being practical. Pagination fixes that by loading the right slice of data without punishing your database or your users.

This tutorial walks through a simple PHP pagination example using MySQL, LIMIT, and OFFSET. I am keeping it straightforward on purpose, because the best pagination code is boring, predictable, and easy to maintain.

How PHP MySQL pagination works

The idea is simple. You decide how many rows to show per page, figure out the current page, and translate that into a SQL query with LIMIT and OFFSET.

$perPage = 20;
$page = max(1, (int) ($_GET['page'] ?? 1));
$offset = ($page - 1) * $perPage;

Page 1 starts at offset 0. Page 2 starts at offset 20. Page 3 starts at offset 40. That is the core pagination math.

A simple PHP pagination example with MySQL

Here is a practical PHP pagination script using PDO and parameterized values:

$perPage = 20;
$page = max(1, (int) ($_GET['page'] ?? 1));
$offset = ($page - 1) * $perPage;

$stmt = $pdo->prepare(
    "SELECT id, name, email
     FROM users
     ORDER BY created_at DESC
     LIMIT :limit OFFSET :offset"
);

$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();

$rows = $stmt->fetchAll();

This is the version of pagination in PHP that I recommend starting with. It is explicit, safe, and easy to explain to someone else six months from now.

Count rows so you can build page links

You also need the total number of records so you can calculate how many pages exist.

$totalRows = (int) $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
$totalPages = (int) ceil($totalRows / $perPage);

Then render links for previous, next, and nearby pages. Keep the UI simple. Fancy pagination controls rarely improve anything.

Build the pagination links

<?php if ($page > 1): ?>
    <a href="?page=<?= $page - 1 ?>">Previous</a>
<?php endif; ?>

<?php for ($i = 1; $i <= $totalPages; $i++): ?>
    <a href="?page=<?= $i ?>"><?= $i ?></a>
<?php endfor; ?>

<?php if ($page < $totalPages): ?>
    <a href="?page=<?= $page + 1 ?>">Next</a>
<?php endif; ?>

Whether you use query strings or prettier routes, the logic is the same. The important part is that users can move through the data without losing context.

Common pagination mistakes in PHP

• Trusting $_GET['page'] without validating it
• Using string interpolation instead of binding integers
• Allowing absurdly large page sizes
• Forgetting an ORDER BY clause and getting unstable results
• Running pagination on a slow query and blaming pagination instead of the query

If your pagination feels slow, the issue is often the underlying query shape, missing indexes, or too much work happening per row. That is where broader performance cleanup helps. See 10 PHP performance pitfalls and how to fix them like a pro for the usual suspects.

Performance considerations for larger result sets

Basic MySQL pagination with LIMIT and OFFSET is fine for a lot of applications. But on very large tables, high offsets can get expensive. If you hit that wall, look at better indexing, leaner SELECT lists, or cursor-based approaches for the heaviest use cases.

And if your result page is slow because every row triggers extra related queries, revisit how you fetch associated data. This is where eager loading in ORM starts to matter.

Keep your PHP pagination script boring

I used to overbuild pagination helpers. These days I prefer a small, readable script that anyone on the team can follow. Good PHP pagination is not clever. It is correct, safe, and easy to maintain.