PHP + SQL
MySQL Pagination Query Pattern
A simple, safe pagination pattern for admin tables, logs and resource lists. Uses PDO prepared statements and a separate COUNT(*) query so you can render proper page controls.
Usage notes
Use this in any listing page: logs, customer tables, blog archives, tools, and more.
- Make sure
created_at(or whatever you order by) is indexed. - Read
?pagefrom the query string and clamp it to at least 1. - Use
$pagesto render your Tailwind pagination UI.
Copy this snippet into your project
Use the full version for learning, or copy it without comments when you just want the bare code.
<?php
declare(strict_types=1);
/** @var PDO $pdo */
$page = max(1, (int)($_GET['page'] ?? 1));
$pageSize = 20;
$offset = ($page - 1) * $pageSize;
// Count total
$total = (int)$pdo->query('SELECT COUNT(*) FROM items')->fetchColumn();
$pages = max(1, (int)ceil($total / $pageSize));
// Fetch page
$stmt = $pdo->prepare('SELECT * FROM items ORDER BY created_at DESC LIMIT :limit OFFSET :offset');
$stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
Tags
#mysql
#pagination
#pdo
#listing