Pagination in PHP with PDO (LIMIT and OFFSET)

Pagination is essential for lists like:

• posts
• products
• users

You should not load 10,000 rows at once.

Step 1: Read page number safely

$page = (int)($_GET['page'] ?? 1);
if ($page < 1) $page = 1;

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

Step 2: Query with LIMIT/OFFSET

Important: LIMIT/OFFSET should be integers, not strings.

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

$rows = $stmt->fetchAll();

Step 3: Simple page links

$next = $page + 1;
$prev = max(1, $page - 1);

<a href="?page=<?php echo $prev; ?>">Prev</a>
<a href="?page=<?php echo $next; ?>">Next</a>

Next: File uploads, the secure way (most beginners do it wrong).