Paginate large tables with cursors in PHP

Approach

Use keyset (cursor) pagination: order by a unique, indexed key (e.g., created_at, id) and fetch rows after the last seen key. Encode the last row's key in an opaque, base64 cursor. Fetch limit + 1 rows to know whether there's a next page.

Solution

<?php
declare(strict_types=1);

function listOrders(PDO $pdo, int $userId, int $limit, ?string $cursor): array
{
    $limit = max(1, min($limit, 100));
    $params = ['user' => $userId, 'limit' => $limit + 1];
    $where = 'user_id = :user';
    if ($cursor !== null) {
        [$createdAt, $id] = json_decode(base64_decode($cursor, true) ?: 'null', true) ?? throw new InvalidArgumentException('Bad cursor');
        $where .= ' AND (created_at, id) < (:created_at, :id)';
        $params += ['created_at' => $createdAt, 'id' => $id];
    }
    $stmt = $pdo->prepare("SELECT id, total_cents, created_at FROM orders WHERE {$where} ORDER BY created_at DESC, id DESC LIMIT :limit");
    foreach ($params as $k => $v) {
        $stmt->bindValue($k, $v, is_int($v) ? PDO::PARAM_INT : PDO::PARAM_STR);
    }
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $next = null;
    if (count($rows) > $limit) {
        array_pop($rows);
        $last = end($rows);
        $next = base64_encode(json_encode([$last['created_at'], (int) $last['id']]));
    }
    return ['items' => $rows, 'next_cursor' => $next];
}
// Index: CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC, id DESC);

Pitfalls

• Ordering by a non-unique column only (ties cause skipped or duplicated rows).
• Missing a matching composite index.
• Row-value comparison (a, b) < (x, y) isn't optimized the same in every database — test with EXPLAIN (MySQL may need the expanded OR form).

Variations

• Offer offset pagination only for small admin tables where jumping to page N matters.
• Sign cursors (HMAC) if you don't want clients to tamper with them.

Further reading: Use The Index, Luke: No offset