Pagination with PHP and MySQL

1. What is Pagination?

Pagination is a technique to split a large number of records into multiple pages. For example, instead of displaying 500 blog posts on a single page, you can show 10 per page and provide next/previous navigation.

2. Why Use Pagination in PHP with MySQL?

  • Improves page load time
  • Better user experience (especially on mobile)
  • Helps with SEO (search engines love well-structured pages)
  • Reduces memory usage and bandwidth

3. SQL LIMIT and OFFSET Explained

MySQL LIMIT controls how many records are fetched, while OFFSET tells MySQL from where to start. 

SELECT * FROM table LIMIT 10 OFFSET 0;  -- Fetch first 10
SELECT * FROM table LIMIT 10 OFFSET 10; -- Fetch next 10

Pagination uses this to calculate which records to show based on the current page number.

4. Pagination System Requirements

  • PHP (7.4+ recommended)
  • MySQL or MariaDB
  • A database table with sample data (e.g., blog posts, users)
  • Basic HTML and CSS

5. Basic PHP MySQL Pagination Example (Step-by-Step)

Step 1: Database Connection

<?php
$conn = new mysqli("localhost", "root", "", "demo_db");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

Step 2: Set Items Per Page

$limit = 10; // Records per page

Step 3: Get Current Page Number

$page = isset($_GET['page']) ? $_GET['page'] : 1;
$start = ($page - 1) * $limit;

Step 4: Fetch Records for Current Page

$result = $conn->query("SELECT * FROM posts LIMIT $start, $limit");

Step 5: Display Records

while ($row = $result->fetch_assoc()) {
    echo "<h3>" . $row['title'] . "</h3>";
    echo "<p>" . $row['content'] . "</p><hr>";
}

Step 6: Pagination Navigation Links

$res = $conn->query("SELECT COUNT(id) AS total FROM posts");
$row = $res->fetch_assoc();
$total = $row['total'];
$pages = ceil($total / $limit);

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

6. Creating Next and Previous Buttons

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

7. Styling Pagination with CSS

<style>
a {
    padding: 8px 12px;
    margin: 2px;
    background-color: #007BFF;
    color: white;
    text-decoration: none;
    border-radius: 5px;
}
a:hover {
    background-color: #0056b3;
}
</style>

8. SEO Best Practices for Pagination

  • Use rel="prev" and rel="next" in <head>
  • Canonical tags to avoid duplicate content
  • Use clear URLs: ?page=2 or /page/2/
<link rel="prev" href="page=1">
<link rel="next" href="page=3">

9. Handling Large Datasets (Advanced Tips)

  • Use indexed columns in SQL queries
  • Avoid SELECT * — select only required fields
  • Use cursor-based pagination for large data
  • Cache page results with Redis or Memcached

10. Bonus: AJAX Pagination in PHP

AJAX allows smooth page loads without refreshing the page.

HTML container for results:

<div id="records"></div>
<div id="pagination"></div>

JavaScript to fetch data:

function loadData(page) {
  fetch("fetch.php?page=" + page)
    .then(res => res.text())
    .then(data => {
      document.getElementById("records").innerHTML = data;
    });
}

Call on page load:

<script>loadData(1);</script>