PHP Code Examples

Create Database Pagination with PHP and PDO

Calculate page offsets, validate query parameters, and retrieve a bounded page of database results.

Example

<?php
$perPage = 10;
$page = filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT);
$page = max(1, $page ?: 1);

$count = (int) $pdo->query(
    'SELECT COUNT(*) FROM articles'
)->fetchColumn();

$totalPages = max(1, (int) ceil($count / $perPage));
$page = min($page, $totalPages);
$offset = ($page - 1) * $perPage;

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

$articles = $stmt->fetchAll();

Explanation

The page number is validated and clamped to the available range. Integer binding is important for LIMIT and OFFSET.

For very large tables, keyset pagination can perform better than high offsets.

Share this page Help other developers discover this resource.