Implementing Pagination for MySQL Results in PHP

Use LIMIT and OFFSET or a keyset cursor to return a bounded ordered MySQL page.

The examples focus on the operation itself, including the important distinction: Keyset pagination avoids scanning skipped rows on large ordered tables.

What It Does

Use the examples below to observe the behavior on a small dataset before extending it to application code.

Before You Start

The examples use PHP 8.2+ features where shown. Database snippets require the corresponding PDO driver, demo tables, and credentials configured outside source code.

Example 1: Basic Use

Keyset pagination uses the previous page’s last ID to avoid scanning many skipped rows.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$lastSeenId = 0;
$stmt = $pdo->prepare("SELECT id, email FROM users WHERE id > :last ORDER BY id LIMIT 20");
$stmt->bindValue(":last", $lastSeenId, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$nextCursor = $rows ? end($rows)["id"] : null;
echo json_encode(["rows" => $rows, "nextCursor" => $nextCursor]);

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 2: Checking Behavior

Keyset pagination uses the previous page’s last ID to avoid scanning many skipped rows.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$lastSeenId = 15;
$stmt = $pdo->prepare("SELECT id, email FROM users WHERE id > :last ORDER BY id LIMIT 20");
$stmt->bindValue(":last", $lastSeenId, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$nextCursor = $rows ? end($rows)["id"] : null;
echo json_encode(["rows" => $rows, "nextCursor" => $nextCursor]);

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Example 3: Practical Example

Keyset pagination uses the previous page’s last ID to avoid scanning many skipped rows.

<?php
$pdo = new PDO(
    "mysql:host=" . (getenv("DB_HOST") ?: "127.0.0.1") . ";dbname=" . (getenv("DB_NAME") ?: "demo") . ";charset=utf8mb4",
    getenv("DB_USER") ?: "app",
    getenv("DB_PASSWORD") ?: "",
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false]
);
$lastSeenId = 40;
$stmt = $pdo->prepare("SELECT id, email FROM users WHERE id > :last ORDER BY id LIMIT 20");
$stmt->bindValue(":last", $lastSeenId, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$nextCursor = $rows ? end($rows)["id"] : null;
echo json_encode(["rows" => $rows, "nextCursor" => $nextCursor]);

This example is meant for a configured application environment; output depends on the installed extension, external service, or database contents.

Important Behavior and Edge Cases

Validate numeric pagination inputs and handle concurrent insertions affecting offset pages.

Comparison with Related Approaches

Common Beginner Mistakes

Key Takeaways

• Use LIMIT and OFFSET or a keyset cursor to return a bounded ordered MySQL page.
• Validate numeric pagination inputs and handle concurrent insertions affecting offset pages.
• Keyset pagination avoids scanning skipped rows on large ordered tables.

Use Implementing Pagination for MySQL Results in PHP when its documented behavior matches the problem. Test the relevant edge cases in your own runtime before deploying it.