Building Pagination for Database Results

Pagination retrieves a bounded page of records rather than loading an entire result set.

The examples focus on the operation itself, including the important distinction: OFFSET pagination is convenient; keyset pagination performs better for deep scrolling.

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

This basic offset-pagination pattern is easy to implement; stable ordering is required.

<?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]
);
$page = 1;
$perPage = 10;
$offset = ($page - 1) * $perPage;
$stmt = $pdo->prepare("SELECT id, email FROM users ORDER BY id LIMIT :take OFFSET :skip");
$stmt->bindValue(":take", $perPage, PDO::PARAM_INT);
$stmt->bindValue(":skip", $offset, PDO::PARAM_INT);
$stmt->execute();
echo json_encode($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

This basic offset-pagination pattern is easy to implement; stable ordering is required.

<?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]
);
$page = 2;
$perPage = 10;
$offset = ($page - 1) * $perPage;
$stmt = $pdo->prepare("SELECT id, email FROM users ORDER BY id LIMIT :take OFFSET :skip");
$stmt->bindValue(":take", $perPage, PDO::PARAM_INT);
$stmt->bindValue(":skip", $offset, PDO::PARAM_INT);
$stmt->execute();
echo json_encode($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

This basic offset-pagination pattern is easy to implement; stable ordering is required.

<?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]
);
$page = 3;
$perPage = 10;
$offset = ($page - 1) * $perPage;
$stmt = $pdo->prepare("SELECT id, email FROM users ORDER BY id LIMIT :take OFFSET :skip");
$stmt->bindValue(":take", $perPage, PDO::PARAM_INT);
$stmt->bindValue(":skip", $offset, PDO::PARAM_INT);
$stmt->execute();
echo json_encode($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

Validate page and page-size limits and always apply deterministic ORDER BY.

Comparison with Related Approaches

Common Beginner Mistakes

Key Takeaways

• Pagination retrieves a bounded page of records rather than loading an entire result set.
• Validate page and page-size limits and always apply deterministic ORDER BY.
• OFFSET pagination is convenient; keyset pagination performs better for deep scrolling.

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