Pagination with PHP

Pagination with PHP What Is Pagination? In simple terms: When a list has hundreds or thousands of entries — products, orders, blog posts — showing them all on one page is slow and overwhelming. Pagination splits the list into pages of a fixed size (for example, 20 items) and lets users move between them with "Previous", "Next", and page-number links.

Technically: Offset pagination combines two queries: a COUNT(*) to know how many items exist (and therefore how many pages), and a SELECT ... ORDER BY ... LIMIT perPage OFFSET (page − 1) × perPage to fetch the current page. The page number comes from the URL (?page=2), so it must be validated. For very large tables, keyset pagination is a faster alternative.

The examples use the five products from the previous lessons, sorted by name: Backpack, Desk Lamp, Gel Pen, Notebook A5, Wireless Mouse.

The Pagination Math <?php function paginate(int $totalItems, int $perPage, int $requestedPage): array { $totalPages = max(1, (int) ceil($totalItems / $perPage)); $page = min(max(1, $requestedPage), $totalPages); $offset = ($page - 1) * $perPage;

return [ 'page' => $page, 'total_pages' => $totalPages, 'per_page' => $perPage, 'offset' => $offset, 'from' => $totalItems === 0 ? 0 : $offset + 1, 'to' => min($offset + $perPage, $totalItems), 'total_items' => $totalItems, ]; }

foreach ([[5, 2, 2], [5, 2, 99], [5, 2, -3], [0, 2, 1]] as [$total, $perPage, $requested]) { $p = paginate($total, $perPage, $requested); printf( "requested %3d → page %d of %d, offset %d, showing %d–%d of %d\n", $requested, $p['page'], $p['total_pages'], $p['offset'], $p['from'], $p['to'], $p['total_items'] ); } Output requested 2 → page 2 of 3, offset 2, showing 3–4 of 5 requested 99 → page 3 of 3, offset 4, showing 5–5 of 5 requested -3 → page 1 of 3, offset 0, showing 1–2 of 5 requested 1 → page 1 of 1, offset 0, showing 0–0 of 0 How It Works Total pages = total items ÷ items per page, rounded up (5 items ÷ 2 per page = 3 pages). At least one page exists even when the list is empty. The requested page is clamped between 1 and the last page, so ?page=99 or ?page=-3 cannot cause errors or empty pages. Offset = (page − 1) × per page: page 1 starts at 0, page 2 at 2, page 3 at 4. from and to produce the familiar "Showing 3–4 of 5" text. Keeping this logic in a pure function makes it easy to test without a database.

Fetching a Page From the Database <?php require DIR . '/src/db.php';

$db = db(); $perPage = 2;

$requestedPage = filter_var($_GET['page'] ?? 1, FILTER_VALIDATE_INT, ['options' => ['default' => 1]]);

$totalItems = (int) $db->query('SELECT COUNT(*) FROM products')->fetch_column(); $p = paginate($totalItems, $perPage, $requestedPage);

$stmt = $db->prepare('SELECT name, price FROM products ORDER BY name, id LIMIT ? OFFSET ?'); $stmt->bind_param('ii', $p['per_page'], $p['offset']); $stmt->execute(); $products = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

echo "Showing {$p['from']}–{$p['to']} of {$p['total_items']} products (page {$p['page']} of {$p['total_pages']})\n";

foreach ($products as $product) { echo "- {$product['name']} (₹{$product['price']})\n"; } Output (for ?page=2) Showing 3–4 of 5 products (page 2 of 3)

• Gel Pen (₹20.00)

• Notebook A5 (₹60.00) Step-by-Step Explanation The page number is validated with FILTER_VALIDATE_INT; invalid input such as ?page=abc falls back to page 1. COUNT(*) determines the total number of items, which paginate() uses to calculate the page and offset. ORDER BY name, id sorts the list. The unique id as tie-breaker guarantees a stable order: without it, products with equal names could swap places between requests, appearing on two pages or on none. LIMIT ? OFFSET ? is bound with bind_param('ii', ...), because LIMIT requires integers. Any filter applied to the list (category, search) must be used in both queries — the count and the page query. Rendering Page Links <?php function paginationLinks(array $p, array $query = []): string { $link = fn(int $page, string $label): string => sprintf( '%s', htmlspecialchars(http_build_query([...$query, 'page' => $page])), $label );

  $parts = [];

  if ($p['page'] > 1) { $parts[] = $link($p['page'] - 1, '« Prev'); }

  for ($i = 1; $i <= $p['total_pages']; $i++) { $parts[] = $i === $p['page'] ? "$i" : $link($i, (string) $i); }

  if ($p['page'] < $p['total_pages']) { $parts[] = $link($p['page'] + 1, 'Next »'); }

  return "

  \n " . implode("\n ", $parts) . "\n\n"; }

echo paginationLinks(paginate(5, 2, 2), ['sort' => 'name']); Output (HTML)

« Prev 1 2 3 Next » How It Works http_build_query() builds each link's query string, keeping other parameters such as the sort order or a search term, and replacing only page. The query string is escaped with htmlspecialchars() (& becomes &), as required inside HTML attributes. The current page is shown as plain text, not as a link; "Prev" and "Next" appear only when there is a previous or next page. For lists with many pages, show only a window of page numbers around the current page (for example, 1 … 7 8 [9] 10 11 … 50) instead of every number.

Keyset Pagination for Large Tables With OFFSET, MySQL must still read and discard all skipped rows: page 5,000 of a large table means skipping 100,000 rows. Keyset pagination (also called the "seek method") continues after the last item of the previous page instead:

<?php require DIR . '/src/db.php';

function productsAfter(mysqli $db, int $afterId, int $limit): array { $stmt = $db->prepare('SELECT id, name FROM products WHERE id > ? ORDER BY id LIMIT ?'); $stmt->bind_param('ii', $afterId, $limit); $stmt->execute();

return $stmt->get_result()->fetch_all(MYSQLI_ASSOC); }

$page = productsAfter(db(), 2, 2); // the previous page ended with ID 2

foreach ($page as $product) { echo "#{$product['id']} {$product['name']}\n"; }

echo "Next cursor: " . end($page)['id'] . "\n"; Output #3 Desk Lamp #4 Backpack Next cursor: 4 The "Next" link carries the last ID (?after=4). Using the primary key index, MySQL jumps directly to the right position — page 5,000 is as fast as page 1.

Offset vs Keyset Pagination Offset (LIMIT … OFFSET …) Keyset (WHERE id > ? … LIMIT …) Jump to any page number Yes No (only next/previous) Speed on deep pages Slows down as the offset grows Constant Stable when rows are inserted or deleted Items can shift between pages Yes Typical use Admin tables, search results with page numbers Infinite scroll, feeds, APIs, exports Keyset pagination with a different sort column (such as created_at) needs a unique tie-breaker: WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC.

Common Mistakes Paginating Without a Stable Order Effect: Items appear twice or never.

Fix: ORDER BY with a unique tie-breaker such as id.

Using the Page Number Without Validation Effect: Errors with negative offsets or empty pages for huge numbers.

Fix: Validate as an integer and clamp to the valid range.

Forgetting Filters in the Count Query Effect: Wrong page counts ("page 3 of 10" when only 2 pages have results).

Fix: Apply the same WHERE conditions to both queries.

Losing Other Query Parameters in Links Effect: Clicking "Next" resets the search or sort order.

Fix: Build links with http_build_query() from the current parameters.

Deep Offset Pagination on Huge Tables Effect: Slow queries.

Fix: Use keyset pagination.

Best Practices Validate and clamp the page number; cap the page size. Sort with a unique tie-breaker for stable pages. Use the same filters for the count and the page query. Preserve other query parameters and escape URLs in links. Use keyset pagination for large datasets, feeds, and APIs. Official reference: https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php