Pagination in PHP: Displaying Data in Chunks for Better Performance 🚀

zeroexp.dev ZeroonLeave a Comment on Pagination in PHP: Displaying Data in Chunks for Better Performance 🚀
Pagination in PHP: Displaying Data in Chunks for Better Performance 🚀

Ever loaded a page with hundreds or thousands of records? 🤯 It’s slow, looks messy, and is bad for performance. That’s why pagination is a must-have in web applications!

In this guide, you’ll learn how to implement pagination in PHP and MySQL with:
✅ Efficient SQL queries
✅ Pagination links (Previous & Next buttons)
✅ A real-world example (Paginated User List)

Let’s dive in! 🚀

🎯 Why Use Pagination?

Without pagination, retrieving thousands of rows can:
❌ Slow down your website
❌ Use too much memory
❌ Make the page hard to read

Solution? Load data in small chunks (e.g., 10 per page) instead of all at once!

1️⃣ Setting Up the Database

First, let’s create a table with sample data.

Run this SQL command:

CREATE DATABASE zeroexp_dev;
USE zeroexp_dev;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

INSERT INTO users (name, email) VALUES
('Zero Dev', 'zero@zeroexp.dev'),
('John Doe', 'john@example.com'),
('Jane Smith', 'jane@example.com'),
('Mike Lee', 'mike@example.com'),
('Alice Brown', 'alice@example.com'),
('Bob Johnson', 'bob@example.com'),
('Sarah Green', 'sarah@example.com'),
('Chris White', 'chris@example.com'),
('David Black', 'david@example.com'),
('Emily Adams', 'emily@example.com'),
('Nathan Scott', 'nathan@example.com'),
('Olivia Parker', 'olivia@example.com');

🔥 What happens?

  • Creates a users table.
  • Inserts dummy users for testing.

2️⃣ Database Connection File

Let’s create a db.php file to connect PHP to MySQL.

db.php

<?php
$host = "localhost";
$dbname = "zeroexp_dev";
$username = "root";
$password = "";

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Database connection failed: " . $e->getMessage());
}
?>

🔥 Why use PDO?
✅ More secure than mysqli_connect()
✅ Supports prepared statements

3️⃣ Fetching Data with Pagination

Now, let’s create users.php, which:

  • Loads users from the database
  • Displays pagination links

users.php

<?php
require "db.php";

// Define the number of results per page
$limit = 5;

// Get the current page number
$page = isset($_GET["page"]) && is_numeric($_GET["page"]) ? $_GET["page"] : 1;

// Calculate the offset for SQL query
$offset = ($page - 1) * $limit;

// Fetch total number of records
$total_stmt = $pdo->query("SELECT COUNT(*) FROM users");
$total_rows = $total_stmt->fetchColumn();
$total_pages = ceil($total_rows / $limit);

// Fetch paginated users
$stmt = $pdo->prepare("SELECT * FROM users ORDER BY id ASC LIMIT :limit OFFSET :offset");
$stmt->bindParam(":limit", $limit, PDO::PARAM_INT);
$stmt->bindParam(":offset", $offset, PDO::PARAM_INT);
$stmt->execute();
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Display users
echo "<h3>User List</h3>";
echo "<table border='1'><tr><th>ID</th><th>Name</th><th>Email</th></tr>";

foreach ($users as $user) {
    echo "<tr><td>{$user['id']}</td><td>{$user['name']}</td><td>{$user['email']}</td></tr>";
}

echo "</table>";

// Pagination Links
echo "<br><div>";

if ($page > 1) {
    echo "<a href='users.php?page=" . ($page - 1) . "'>Previous</a> ";
}

for ($i = 1; $i <= $total_pages; $i++) {
    echo "<a href='users.php?page=$i'>$i</a> ";
}

if ($page < $total_pages) {
    echo "<a href='users.php?page=" . ($page + 1) . "'>Next</a>";
}

echo "</div>";
?>

🔥 How does this work?
✅ Calculates the total number of pages using COUNT(*)
✅ Fetches only 5 users per page with LIMIT and OFFSET
✅ Displays pagination links

4️⃣ Understanding SQL LIMIT & OFFSET

✅ How Pagination Works in MySQL

Page Number SQL Query Used
Page 1 LIMIT 5 OFFSET 0
Page 2 LIMIT 5 OFFSET 5
Page 3 LIMIT 5 OFFSET 10

5️⃣ Styling the Pagination

Let’s improve the design using CSS.

Add this to <head> in users.php

<style>
    a {
        text-decoration: none;
        padding: 5px 10px;
        background: #007bff;
        color: white;
        border-radius: 5px;
        margin: 0 5px;
    }
    a:hover {
        background: #0056b3;
    }
    .current-page {
        font-weight: bold;
        color: red;
    }
</style>

Update Pagination Links in users.php

for ($i = 1; $i <= $total_pages; $i++) {
    $class = ($page == $i) ? "class='current-page'" : "";
    echo "<a href='users.php?page=$i' $class>$i</a> ";
}

🔥 What happens?

  • Highlights the current page number in red.

🎯 Mini Project: Paginated Blog Posts

Let’s modify the pagination system for a blog.

SQL Table for Blog Posts

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Modify users.php to Fetch Blog Posts

$stmt = $pdo->prepare("SELECT * FROM posts ORDER BY created_at DESC LIMIT :limit OFFSET :offset");

🔥 Boom! Now you have a paginated blog system! 🚀

🚀 Final Thoughts

Now you know how to implement pagination like a pro!
✅ Use LIMIT and OFFSET for efficient queries
✅ Prevent slow page loads by loading only a few records per page
✅ Use pagination links for better user experience

👉 Next: Introduction to Object-Oriented PHP

Happy coding! 🎉🚀

Leave a Reply