---

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

---

Related tutorial:

• PHP MySQL Insert Data
• PHP MySQL Select Data
• PHP MySQL Update Data
• PHP MySQL Use WHERE

---

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.

# Summary

Here is what you've learned in this lesson:

• PHP MySQL Pagination Full Tutorial – Step-by-Step Guide
• What Is Pagination in PHP MySQL?
• Why Pagination Is Important in PHP Applications
• Pagination Prerequisites
• Sample MySQL Table Used for Pagination
• PHP MySQL Database Connection Code
• Pagination Logic Explained Simply
• Step 1: Decide Records Per Page
• Step 2: Get Current Page Number
• Step 3: Calculate OFFSET
• Step 4: Fetch Paginated Data Using LIMIT
• Step 5: Count Total Records in Table
• Step 6: Calculate Total Pages
• Step 7: Create Pagination Links
• Complete Pagination Example in One File
• Pagination with ORDER BY Clause
• Secure Pagination Using Prepared Statements
• Common Pagination Mistakes
• Pagination Best Practices
• Related tutorial:
• Frequently Asked Questions (FAQ)