PHP MySQL Pagination Full Tutorial – Create Powerful & Fast Pagination

Table of Contents:

PHP MySQL Pagination Full Tutorial – Step-by-Step Guide

Pagination is a critical feature in modern web applications. It allows data to be displayed in smaller, manageable chunks, improving performance and user experience.

This tutorial explains how to build a complete pagination system in PHP and MySQL, starting from basics to real-world implementation.

What Is Pagination in PHP MySQL?

Pagination is the process of dividing large datasets into multiple pages instead of displaying all records at once.

Example:

Page 1 → Records 1–10
Page 2 → Records 11–20
Page 3 → Records 21–30

Why Pagination Is Important in PHP Applications

Pagination helps to:

Improve website speed
Reduce server load
Enhance user experience
Display large datasets efficiently
Build professional admin panels

Pagination Prerequisites

Before implementing pagination, ensure you have:

PHP installed
MySQL database
Database connection file
A table with multiple records

Sample MySQL Table Used for Pagination

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

PHP MySQL Database Connection Code

<?php
$conn = mysqli_connect("localhost", "root", "", "testdb");

if (!$conn) {
    die("Database connection failed");
}
?>

Pagination Logic Explained Simply

Pagination uses two important values:

LIMIT → How many records per page
OFFSET → How many records to skip

Formula:

OFFSET = (current_page - 1) × records_per_page

Step 1: Decide Records Per Page

$records_per_page = 5;

Step 2: Get Current Page Number

$page = isset($_GET['page']) ? $_GET['page'] : 1;

Step 3: Calculate OFFSET

$offset = ($page - 1) * $records_per_page;

Step 4: Fetch Paginated Data Using LIMIT

<?php
$query = "SELECT * FROM users LIMIT $offset, $records_per_page";
$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . "<br>";
}
?>

Output (Page 1):

Rahul
Amit
Neha
Suresh
Pooja

Step 5: Count Total Records in Table

<?php
$total_query = "SELECT COUNT(*) AS total FROM users";
$total_result = mysqli_query($conn, $total_query);
$total_row = mysqli_fetch_assoc($total_result);

$total_records = $total_row['total'];
?>

Step 6: Calculate Total Pages

<?php
$total_pages = ceil($total_records / $records_per_page);
?>

Step 7: Create Pagination Links

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

Output:

1 2 3 4

Complete Pagination Example in One File

<?php
$conn = mysqli_connect("localhost", "root", "", "testdb");

$records_per_page = 5;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$offset = ($page - 1) * $records_per_page;

$result = mysqli_query($conn,
    "SELECT * FROM users LIMIT $offset, $records_per_page");

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'] . "<br>";
}

$total = mysqli_fetch_assoc(mysqli_query($conn,
    "SELECT COUNT(*) AS total FROM users"))['total'];

$total_pages = ceil($total / $records_per_page);

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

Pagination with ORDER BY Clause

SELECT * FROM users ORDER BY id DESC LIMIT $offset, $records_per_page;

This ensures consistent and predictable pagination results.

Secure Pagination Using Prepared Statements

<?php
$stmt = mysqli_prepare($conn,
    "SELECT * FROM users LIMIT ?, ?");
mysqli_stmt_bind_param($stmt, "ii", $offset, $records_per_page);

mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

while ($row = mysqli_fetch_assoc($result)) {
    echo $row['email'] . "<br>";
}
?>

Common Pagination Mistakes

Not using ORDER BY
Allowing invalid page numbers
Forgetting COUNT query
Displaying all records accidentally

PHP MySQL pagination, PHP pagination tutorial, MySQL LIMIT OFFSET pagination, PHP pagination example, PHP MySQL paging system, PHP database tutorial

Pagination Best Practices

Always validate page number
Use LIMIT with ORDER BY
Index primary key columns
Use pagination for large datasets
Avoid loading unnecessary data

Frequently Asked Questions (FAQ)

1. What is pagination in PHP?

Pagination splits data across multiple pages.

2. Is pagination necessary?

Yes, for performance and usability.

3. Can pagination work with search results?

Yes, pagination is commonly used with search.

4. What is the best records per page count?

Usually between 5 to 20 records.

5. Does pagination improve website speed?

Yes, significantly for large datasets.

Comments

Leave a Reply Cancel reply