Pagination in PHP with MySQL

Why pagination

When a table has hundreds of rows, showing them all on one page is slow and hard to read. Pagination splits the results into pages — say 10 per page — with links to move between them. It keeps pages fast and gives users a clean way to browse.

The idea: LIMIT and OFFSET

LIMIT says how many rows to return; OFFSET says how many to skip first.

-- page 1: first 10 rows
SELECT * FROM products LIMIT 10 OFFSET 0;

-- page 2: skip 10, take next 10
SELECT * FROM products LIMIT 10 OFFSET 10;

Each page changes only the OFFSET. Page 3 would skip 20, page 4 would skip 30, and so on.

Calculating the offset

The offset comes from the page number with a simple formula.

<?php
  $perPage = 10;
  $page = max(1, (int)($_GET["page"] ?? 1));
  $offset = ($page - 1) * $perPage;
  // page 3 -> offset = (3-1)*10 = 20
?>

We cast page to an integer for safety and use max(1, ...) so the page is never below 1. The formula (page - 1) * perPage gives the rows to skip.

The paginated query

<?php
  // total pages
  $total = $pdo->query("SELECT COUNT(*) FROM products")->fetchColumn();
  $totalPages = ceil($total / $perPage);

  // rows for this page
  $stmt = $pdo->prepare("SELECT * FROM products LIMIT ? OFFSET ?");
  $stmt->bindValue(1, $perPage, PDO::PARAM_INT);
  $stmt->bindValue(2, $offset, PDO::PARAM_INT);
  $stmt->execute();
  $rows = $stmt->fetchAll();
?>

First we count all rows and divide by per-page (rounded up with ceil) to get the total pages. Then we fetch just this page's rows using LIMIT and OFFSET bound as integers.

Building page links

<?php
  for ($i = 1; $i <= $totalPages; $i++) {
      echo "<a href='?page=$i'>$i</a> ";
  }
?>

A simple loop prints a link for each page. Clicking a number sets ?page=N in the URL, which your offset calculation reads to show that page.

Common mistakes

• Forgetting to cast the page number. Use (int) so LIMIT/OFFSET stay safe.
• Allowing page 0 or negative. Use max(1, ...) to keep the page valid.
• Not rounding total pages up. Use ceil(), or the last few rows get no page.
• Binding LIMIT as a string. Bind with PDO::PARAM_INT so MySQL accepts it.

Summary

• Pagination splits results into pages for speed and readability.
• LIMIT sets how many rows; OFFSET sets how many to skip.
• Offset formula: (page - 1) * perPage.
• Total pages: ceil(totalRows / perPage).
• Loop to print numbered page links using ?page=N.

Next, run the queries safely with prepared statements, or read results with PHP CRUD.