Pagination

Why Pagination

Pagination prevents loading too much data at once and improves performance. The classic approach is LIMIT + OFFSET.

Basic LIMIT/OFFSET

Always validate and cast page/limit from request. Never trust raw query strings.

<?php
$page = (int)($_GET["page"] ?? 1);
$limit = (int)($_GET["limit"] ?? 20);

if ($page < 1) $page = 1;
if ($limit < 1 || $limit > 100) $limit = 20;

$offset = ($page - 1) * $limit;

$stmt = $pdo->prepare("SELECT id, title FROM items ORDER BY id DESC LIMIT ? OFFSET ?");
$stmt->bindValue(1, $limit, PDO::PARAM_INT);
$stmt->bindValue(2, $offset, PDO::PARAM_INT);
$stmt->execute();

$rows = $stmt->fetchAll();

Return Pagination Metadata

APIs often return page/limit plus total. Total count is an extra query.

<?php
$total = (int)$pdo->query("SELECT COUNT(*) FROM items")->fetchColumn();

$response = [
  "ok" => true,
  "pagination" => [
    "page" => $page,
    "limit" => $limit,
    "total" => $total,
  ],
  "data" => $rows,
];

header("Content-Type: application/json; charset=utf-8");
echo json_encode($response);

Performance Note

OFFSET can get slower on deep pages. A more scalable approach is keyset pagination (WHERE id < last_id ORDER BY id DESC LIMIT N). We can add that later as an advanced item if you want.

Production Tip

Always bound limit to prevent abuse. Use indexes on sort columns. Consider caching popular pages and using keyset pagination for large datasets.