Complete Guide to PHP Pagination with MySQL

PHP and MySQL power over 75% of websites worldwide, making efficient pagination essential for scalable, responsive, and high-performance web development.

Key Points

PHP is used by 76.5% of all websites with a known server-side language.
MySQL is the most popular database, used by 45.7% of all websites.
Pagination improves load times by up to 70% in data-heavy apps.

Jr. Web Developer

Lovepreet

3 min read

Skilled web developer with 2+ years of hands-on experience delivering fast and functional websites.

Image displaying PHP and MySQL logos with navigation buttons below, indicating a web development context

Pagination is a crucial component of any high-quality web application that displays large datasets. By breaking down content into smaller, manageable pages, pagination improves performance and enhances user experience. Whether you're building custom web development solutions or offering website development services, mastering pagination is a fundamental skill for developers, especially when working with PHP and MySQL.

In this article, we’ll walk you through a complete example of implementing pagination in PHP using both MySQLi and PDO. This guide is ideal for anyone seeking to hire PHP developers or back-end developers who require structured, scalable backend logic.

Step 1: Connect to Your Database

Using PDO (for secure and flexible database interaction):

                                        $pdo = new PDO("mysql:host=localhost;dbname=your_database", "username", "password");

Or using MySQLi:
$conn = new mysqli("localhost", "root", "", "your_database");
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

Step 2: Set Pagination Variables

Define how many records you want to display per page and calculate the offset:

                                        $limit = 10; // records per page
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$start = ($page - 1) * $limit;

Step 3: Get the Total Number of Records

This is essential for determining how many pages to generate in your pagination UI.

Using PDO:

                                        $stmt = $pdo->query("SELECT COUNT(*) FROM users");
$total_results = $stmt->fetchColumn();
$total_pages = ceil($total_results / $limit);

Using MySQLi:

                                        $result = $conn->query("SELECT COUNT(*) AS total FROM users");
$row = $result->fetch_assoc();
$total_pages = ceil($row['total'] / $limit);

Step 4: Fetch Data for the Current Page

With parameter binding

PDO version:

                                        $sql = "SELECT * FROM users LIMIT :start, :limit";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':start', $offset, PDO::PARAM_INT);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

MySQLi version:

                                        $sql = "SELECT * FROM users LIMIT $offset, $limit";
$result = $conn->query($sql);

Step 5: Display Data and Pagination Links

Here’s a complete front-end example with styling and PHP logic to render results and pagination controls:

                                        <!DOCTYPE html>
<html>
<head>
    <title>PHP Pagination Example | Custom Web Development</title>
    <style>
        table { width: 60%; margin: 20px auto; border-collapse: collapse; }
        th, td { padding: 10px; border: 1px solid #ddd; }
        .pagination { text-align: center; margin-top: 20px; }
        .pagination a {
            padding: 6px 12px;
            margin: 0 3px;
            border: 1px solid #aaa;
            text-decoration: none;
            color: #333;
        }
        .pagination a.active {
            background: #333;
            color: #fff;
            border-color: #333;
        }
    </style>
</head>
<body>

<h2 style="text-align:center;">User List</h2>

<table>
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Email</th>
    </tr>
    <?php while ($row = $result->fetch_assoc()): ?>
    <tr>
        <td><?= htmlspecialchars($row['id']) ?></td>
        <td><?= htmlspecialchars($row['name']) ?></td>
        <td><?= htmlspecialchars($row['email']) ?></td>
    </tr>
    <?php endwhile; ?>
</table>

<div class="pagination">
    <?php if ($page > 1): ?>
        <a href="?page=<?= $page - 1 ?>">Prev</a>
    <?php endif; ?>

    <?php for ($i = 1; $i <= $total_pages; $i++): ?>
        <a class="<?= ($i == $page) ? 'active' : '' ?>" href="?page=<?= $i ?>"><?= $i ?></a>
    <?php endfor; ?>

    <?php if ($page < $total_pages): ?>
        <a href="?page=<?= $page + 1 ?>">Next</a>
    <?php endif; ?>
</div>

</body>
</html>

Why This Matters for Your Development Projects

If you’re offering website development services or engaged in custom web development, effective pagination is a must. It not only optimizes server performance but also enhances usability for end-users, especially in data-heavy applications like admin panels, CRM systems, or e-commerce dashboards.

When you hire Laravel developers or experienced front-end developers, you ensure that best practices like secure queries, accessible navigation, and responsive design are implemented right from the start.

Final Words

Mastering pagination helps create scalable, efficient, and user-friendly websites. Whether you are building your custom application or delivering website development services for clients, this guide sets the groundwork for implementing pagination cleanly and effectively. Pair it with Laravel's built-in pagination features or front-end frameworks for even more powerful results. Need professional support? Consider hiring Laravel developers or skilled front-end developers to bring advanced pagination and UI/UX capabilities into your project.