How to Implement Pagination in PHP and MySQL

Overview

Pagination is an essential tool for web developers when it comes to displaying long lists or arrays of data without overwhelming the user. Implementing pagination can improve performance by reducing server load and enhancing the user experience by providing a manageable chunk of results at once. In this tutorial, we will walk through how to implement basic pagination in PHP and MySQL.

Prerequisites:

• Basic knowledge of PHP and MySQL
• Access to a development environment with PHP and MySQL installed
• A MySQL database with a table containing data to display

Let’s Do It

Step 1: Setting up the MySQL Database

Before we start with the pagination, ensure that there is a table in your MySQL database with some data in it. For demonstration purposes, let’s assume you have a table named items with at least two columns: id and name. It will look something like this:

CREATE TABLE `items` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(100) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Step 2: Establishing a Connection to MySQL

In your PHP script, the first thing to do is establish a connection to the MySQL database:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$database = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
 die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

Step 3: Determining the Page Number and Size

The page number indicates the current page, and the page size denotes how many records to show per page. Suppose we pass these via a GET request:

<?php
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$pageSize = isset($_GET['pageSize']) ? (int)$_GET['pageSize'] : 10;

$limit = $pageSize;
$offset = ($page - 1) * $pageSize;
?>

Step 4: Fetching Data with Limits

To fetch the data with limits, we will write a MySQL query that fetches a specified range:

<?php
$sql = "SELECT * FROM items LIMIT $offset, $limit";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
 // We'll output the data of each row
 while($row = $result->fetch_assoc()) {
 echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
 }
} else {
 echo "0 results";
}
$conn->close();
?>

Step 5: Creating the Pagination Links

Using PHP again, we can create links to each page that passes the corresponding page number:

<?php
$query = "SELECT COUNT(id) AS num_rows FROM items";
$result = $conn->query($query);
$row = $result->fetch_assoc();
$totalRows = $row['num_rows'];
$totalPages = ceil($totalRows / $pageSize);

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

Conclusion

Pagination is a critical component for any data driven web application. This tutorial has covered the basics of implementing pagination using PHP and MySQL. This should give you a foundation to build upon and implement more complex pagination features such as page number ellipsis, first/last page links, and dynamic page sizes.

Remember that while we have kept security relatively basic for this tutorial, always consider using prepared statements to prevent SQL injection when using variables in SQL queries.