Limit Data

Limiting and Paginating MySQL Data in PHP

MySQL's LIMIT and OFFSET clauses let PHP applications return small, fast pages of results (such as 20 products per page) instead of loading entire tables into memory. Pairing LIMIT with an explicit ORDER BY and integer parameter binding ensures every page displays consistent, predictable records.

Implementing Clean Offset Pagination in PHP

Always compute offset = (page - 1) * perPage, clamp page >= 1 and perPage <= 100, and bind both LIMIT and OFFSET with PDO::PARAM_INT.

<?php
declare(strict_types=1);

function getLessonsPage(PDO $pdo, int $page = 1, int $perPage = 15): array
{
    $safePage = max(1, $page);
    $safePerPage = min(100, max(1, $perPage));
    $offset = ($safePage - 1) * $safePerPage;

    $stmt = $pdo->prepare(
        'SELECT id, title, slug
         FROM lessons
         WHERE is_active = 1
         ORDER BY id ASC
         LIMIT :limit OFFSET :offset'
    );
    $stmt->bindValue(':limit', $safePerPage, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();

    return $stmt->fetchAll();
}

Keyset (Cursor) Pagination for Large Tables

Offset pagination gets slower on deep pages (like OFFSET 100000) because MySQL must read and discard the first 100,000 rows before returning 15. For infinite feeds or large API lists, use keyset pagination: track the last seen id and query WHERE id > :last_id ORDER BY id ASC LIMIT :limit, which jumps straight to the next page using the primary index in milliseconds.

Common Mistakes to Avoid

• Using LIMIT without ORDER BY: Without a deterministic ORDER BY (including a unique tie-breaker like id), rows can jump between Page 1 and Page 2 or appear twice.
• Passing $limit inside $stmt->execute([$limit]): When PDO binds values as strings by default, MySQL may reject LIMIT '15' with a syntax error. Always bind LIMIT and OFFSET with PDO::PARAM_INT.
• Allowing clients to pass ?per_page=999999: Always cap $perPage on the server (for example, max 100) to prevent denial-of-service memory spikes.

Frequently Asked Questions

What does LIMIT 15, 30 mean compared to LIMIT 30 OFFSET 15?

LIMIT 15, 30 uses MySQL's shorthand LIMIT offset, count, which is easy to misread backwards. Always write LIMIT :limit OFFSET :offset for clarity.

How do I calculate total page count for pagination UI?

Run SELECT COUNT(*) FROM lessons WHERE is_active = 1, then compute (int) ceil($totalRows / $safePerPage).

When should I switch from OFFSET to keyset pagination?

Switch to keyset (WHERE id > :cursor) whenever users or API consumers paginate past a few thousand rows on growing tables.