Code, Leadership & the Remote Dev Life

How to Implement MySQL Pagination in PHP (Step-by-Step Guide)

A visually clear, developer-friendly image that reflects the concept of paginated data in a PHP/MySQL environment.

Written by

Mike Lopez

in

Pagination: it’s how you turn “Here’s a mullion rows” into “Here’s 20 rows per page” without melting your server.

Alright, before we dive in—this post assumes you already know how to connect to MySQL using PHP’s PDO. If that sounds like gibberish, hit pause and go learn that first. I’ll wait. 😎

Still here? Great. Let’s talk about pagination—the art of not dumping all your data on one page like it’s 1999.

If you’re building PHP apps with MySQL, you’ll run into it sooner or later:

$stmt = $pdo->prepare("SELECT * FROM table_name ORDER BY id LIMIT 20 OFFSET 40");
$stmt->execute();
$rows = $stmt->fetchAll();

That’s paginating the records in table_name, fetching 20 results per page, starting at record 41. It’s one of the first things I teach when mentoring junior devs — because once your database grows, a simple SELECT * won’t cut it.

Here’s how I typically break it down when onboarding someone:

1. Understand the Math

Each “page” corresponds to a LIMIT (number of rows) and an OFFSET (where to start). Page 1? OFFSET 0. Page 2? OFFSET 20. And so on.

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

2. Parameterize Your Queries

Always use prepared statements — pagination is no excuse to get lazy with security.

$stmt = $pdo->prepare("SELECT * 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();

3. Count for Total Pages

You’ll need this for rendering page links.

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

4. Handle Edge Cases

  • Page doesn’t exist? Redirect to page 1.
  • Per page too high? Cap it.
  • Negative page? Bad user. Redirect.

5. Build Frontend Links

Whether you’re using query strings (?page=2) or clean URLs (/users/page/2), your frontend should help users navigate easily.

I used to make this overly complex, writing custom pagination libraries that ended up being more work than help. These days? I keep it simple, readable, and maintainable. Bonus if I can wrap it up in a helper function or class.

If you’re building plugins or small admin tools, this is a must-have. Poor pagination = poor UX = sad users.

Don’t let that be you.

More posts