Limit Data

Pagination & Result Limits (LIMIT & OFFSET)

The LIMIT and OFFSET clauses restrict the number of rows returned by a SELECT query, enabling web application database pagination.

Pagination Math Formula

$limit = 10; // Items per page
$page = 3;   // Current page number
$offset = ($page - 1) * $limit; // (3 - 1) * 10 = 20
// SQL: LIMIT 10 OFFSET 20

Pagination Flowchart

flowchart TD
    A["User Requests Page Number $page"] --> B["Calculate Offset: ($page - 1) * $limit"]
    B --> C["Execute SQL: SELECT * FROM items LIMIT :limit OFFSET :offset"]
    C --> D["Fetch Page Data Slice"]
    D --> E["Render Data Grid & Pagination Controls"]

Practical Code Example

<?php
declare(strict_types=1);

function getPaginatedUsers(PDO $pdo, int $page = 1, int $perPage = 10): array
{
    $page = max(1, $page);
    $perPage = max(1, min(100, $perPage)); // Clamp between 1 and 100
    $offset = ($page - 1) * $perPage;

    $sql = "SELECT id, username, email FROM users ORDER BY id DESC LIMIT :limit OFFSET :offset";
    $stmt = $pdo->prepare($sql);

    // Note: Bind LIMIT and OFFSET as explicit integers when emulated prepares are off!
    $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

try {
    $pdo = new PDO("mysql:host=127.0.0.1;dbname=ecommerce_db;charset=utf8mb4", 'root', 'secret_password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false
    ]);

    $pageData = getPaginatedUsers($pdo, page: 2, perPage: 5);
    echo "--- Fetched Page #2 Data Slice (" . count($pageData) . " items) ---
";
    print_r($pageData);

} catch (PDOException $e) {
    die("Pagination Query Error: " . $e->getMessage());
}

Best Practices

• Explicitly Bind LIMIT and OFFSET as PDO::PARAM_INT: When native prepared statements are enabled, MySQL requires LIMIT arguments to be integer types rather than string quotes.
• Clamp User Input Page & PerPage Values: Protect against arbitrary large perPage inputs (min(100, $perPage)) to prevent Memory Exhaustion DoS attacks.
• Always Include an ORDER BY Clause: LIMIT results are non-deterministic unless paired with a consistent ORDER BY column (e.g. ORDER BY id DESC).

Self-Check Challenge

Calculate the SQL OFFSET value for page 4 with 15 items per page! ((4 - 1) * 15 = 45)