How to paginate database results
Short answer
LIMIT sets the page size, OFFSET how many rows to skip:
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price INTEGER)');
$pdo->exec("INSERT INTO products (title, price) VALUES ('Tea', 250), ('Coffee', 400), ('Juice', 180), ('Green tea', 300)");
$page = 2;
$perPage = 2;
$statement = $pdo->prepare('SELECT title FROM products ORDER BY id LIMIT :limit OFFSET :offset');
$statement->bindValue('limit', $perPage, PDO::PARAM_INT);
$statement->bindValue('offset', ($page - 1) * $perPage, PDO::PARAM_INT);
$statement->execute();
echo implode(', ', $statement->fetchAll(PDO::FETCH_COLUMN));Output:
Juice, Green teaThe number of pages
Divide the total number of records by the page size:
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price INTEGER)');
$pdo->exec("INSERT INTO products (title, price) VALUES ('Tea', 250), ('Coffee', 400), ('Juice', 180), ('Green tea', 300)");
$total = (int) $pdo->query('SELECT COUNT(*) FROM products')->fetchColumn();
echo (int) ceil($total / 3);Output:
2Two requirements
Pass the numbers for LIMIT and OFFSET through bindValue() with PDO::PARAM_INT, and always specify ORDER BY.
Functions used in this recipe
- PDO — A database connection.
- PDOStatement — A prepared statement and its result.
- ceil — Rounds a number up to an integer.