Implementing Database Pagination Safely

Limit database scans using validated page bounds and stable sorting.

The examples focus on the operation itself, including the important distinction: Keyset pagination uses the last seen ordered key for efficient continuation.

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

Caps page size and offset and binds integer pagination values. On very large tables keyset pagination is preferable.

<?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]
);
$requestedPage = 1;
$page = max(1, min(1000, $requestedPage));
$limit = 20;
$offset = ($page - 1) * $limit;
$stmt = $pdo->prepare("SELECT id, email FROM users ORDER BY id LIMIT :n OFFSET :skip");
$stmt->bindValue(":n", $limit, PDO::PARAM_INT);
$stmt->bindValue(":skip", $offset, PDO::PARAM_INT);
$stmt->execute();
echo count($stmt->fetchAll(PDO::FETCH_ASSOC));

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

Example 2: Checking Behavior

Caps page size and offset and binds integer pagination values. On very large tables keyset pagination is preferable.

<?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]
);
$requestedPage = 2;
$page = max(1, min(1000, $requestedPage));
$limit = 20;
$offset = ($page - 1) * $limit;
$stmt = $pdo->prepare("SELECT id, email FROM users ORDER BY id LIMIT :n OFFSET :skip");
$stmt->bindValue(":n", $limit, PDO::PARAM_INT);
$stmt->bindValue(":skip", $offset, PDO::PARAM_INT);
$stmt->execute();
echo count($stmt->fetchAll(PDO::FETCH_ASSOC));

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

Example 3: Practical Example

Caps page size and offset and binds integer pagination values. On very large tables keyset pagination is preferable.

<?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]
);
$requestedPage = 3;
$page = max(1, min(1000, $requestedPage));
$limit = 20;
$offset = ($page - 1) * $limit;
$stmt = $pdo->prepare("SELECT id, email FROM users ORDER BY id LIMIT :n OFFSET :skip");
$stmt->bindValue(":n", $limit, PDO::PARAM_INT);
$stmt->bindValue(":skip", $offset, PDO::PARAM_INT);
$stmt->execute();
echo count($stmt->fetchAll(PDO::FETCH_ASSOC));

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

A very large OFFSET is expensive; avoid arbitrary client-provided ORDER BY strings.

Comparison with Related Approaches

Common Beginner Mistakes

Key Takeaways

• Limit database scans using validated page bounds and stable sorting.
• A very large OFFSET is expensive; avoid arbitrary client-provided ORDER BY strings.
• Keyset pagination uses the last seen ordered key for efficient continuation.

Use Implementing Database Pagination Safely when its documented behavior matches the problem. Test the relevant edge cases in your own runtime before deploying it.