Implementing Data Pagination in PHP with PDO: A Step‑by‑Step Guide

When developing web applications, pagination is a common requirement to improve load speed and user experience; using PHP Data Objects (PDO) makes implementing paginated queries straightforward.

1. Create database and table – The example creates a mydb database and a students table with fields id, name, gender, and age:

CREATE DATABASE IF NOT EXISTS mydb;
USE mydb;

CREATE TABLE IF NOT EXISTS students (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  gender varchar(10) NOT NULL,
  age int(11) NOT NULL,
  PRIMARY KEY (id)
);

2. Connect to the database with PDO – Define connection parameters, instantiate PDO, set error mode to exceptions, and run a simple SELECT query to fetch all rows:

<?php
$host = "localhost";
$dbName = "mydb";
$user = "root";
$pass = "password";

try {
    $db = new PDO("mysql:host=$host;dbname=$dbName", $user, $pass);
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

$query = "SELECT * FROM students";
$stmt = $db->prepare($query);
$stmt->execute();
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

3. Pagination logic – Define the number of records per page, calculate total records, total pages, current page (from $_GET['page']), and the offset for the LIMIT clause:

<?php
$perPage = 10; // records per page
$total = count($students); // total records
$pages = ceil($total / $perPage); // total pages

$page = isset($_GET['page']) ? $_GET['page'] : 1; // current page, default 1
$start = ($page - 1) * $perPage; // offset

$query = "SELECT * FROM students LIMIT $start, $perPage";
$stmt = $db->prepare($query);
$stmt->execute();
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($students as $student) {
    echo $student['name'] . ", " . $student['gender'] . ", " . $student['age'] . "<br/>";
}

// pagination links
for ($i = 1; $i <= $pages; $i++) {
    echo "<a href='?page=$i'>$i</a> ";
}
?>

The script first sets the page size to 10, computes the total number of rows and pages using ceil(), retrieves the current page number, calculates the starting offset, and then queries the subset of records with a LIMIT clause. It outputs each student's data and generates simple numeric links to navigate between pages.

By leveraging PDO for database interaction and PHP for pagination calculations, developers can efficiently implement paginated data displays in their web applications.