Implement Simple Pagination in PHP
Pagination refers to splitting large data records into smaller chunks to save database and server resources and improve performance. Learn how to paginate MySQL data in PHP with a simple PHP pagination example.
What is Pagination in PHP, and Why Use It?
Pagination in PHP is a technique used to split a large number of data records into small chunks to display limited information to users. A PHP pagination helps to avoid slow page load when a large number of records are retrieved from the database. For example, you might have a huge number of products, users, or articles. So instead of showing them all on one page, we can show a limited number of records and show them as different pages with navigation to each page. This not only improves load time but also provides a better user experience.
How to Create Simple Pagination in PHP with MySQL?
This post demonstrates how to implement pagination in PHP in simple and easy steps, with a working demo example. Steps to be followed to paginate MySQL data in PHP:
- Get the total number of records in the database table.
- Set the number of items to be displayed per page.
- Then use the total number of records and items per page to calculate how many pages you need.
- Fetch the exact number of records from the database table, providing the offset and limit of records based on the current page number and items per page limit.
- Add a for loop and run the iteration for the number of times equal to the total number of pages. Each page will have a link except for the current page, the previous page if the current page is 1, and the next page if the current page is equal to the total number of pages, i.e. the current page is the last page.
Files we are going to need for this pagination are:
- employees.sql: Contains database records for the employees table.
- constants.php: Contains the required constants for the database connection and queries.
- index.php: Will be retrieving employees' records from the database and paginating them.
- style.css: Will contain the styles for our index.php and pagination.
Import Employees Sample Data to Table
Our employees.sql is the SQL file that we will need to import. It will
create the employees table and insert sample records for our pagination. The sample records below were generated using the fakerphp/php composer package.
employees.sql
CREATE TABLE IF NOT EXISTS `employees` (
`id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
`full_name` varchar(100) NOT NULL,
`email` varchar(150) DEFAULT NULL,
`gender` tinytext DEFAULT NULL,
`designation` varchar(100) DEFAULT NULL
);
INSERT INTO `employees` (`id`, `full_name`, `email`, `gender`, `designation`) VALUES
(1, 'Dr. Humberto Stracke', '[email protected]', 'Male', 'Social Sciences Teacher'),
(2, 'Jaren Emard I', '[email protected]', 'Male', 'Set and Exhibit Designer'),
(3, 'Furman Marvin', '[email protected]', 'Male', 'Mapping Technician'),
(4, 'Miss Shana Wiegand', '[email protected]', 'Female', 'Dietetic Technician'),
(5, 'Gregorio Hayes', '[email protected]', 'Male', 'Railroad Switch Operator'),
(6, 'Caesar Jenkins', '[email protected]', 'Male', 'Painting Machine Operator'),
(7, 'Graciela Schaefer', '[email protected]', 'Female', 'Medical Assistant'),
(8, 'Johnpaul Kuvalis', '[email protected]', 'Male', 'House Cleaner'),
(9, 'Amos Thiel', '[email protected]', 'Male', 'General Farmworker'),
(10, 'Nona Kovacek', '[email protected]', 'Female', 'Precision Mold and Pattern Caster');
Add Database Connection Constants
We add some constants for the website URL and database credentials to a constants.php file. We will use these constants in our index.php file to connect to the database and generate pagination links.
constants.php
<?php
define('BASE_URL', 'https://' . $_SERVER['SERVER_NAME']); // Base URL of App
define('DB_HOST', 'DATABASE_HOST'); // Your database host
define('DB_NAME', 'DATABASE_NAME'); // Your database name
define('DB_USER', 'DATABASE_USERNAME'); // Username for database
define('DB_PASSWORD', 'DATABASE_PASSWORD'); // Password for database
Paginate MySQL Table Records and Create Page Links
Core PHP pagination script that connects to the database, calculates pages, and displays page links. We connect to our database and show paginated results on the same page.
We gracefully handle the current page number if the user enters an invalid page number in the URL, like -1 or any page number greater than the total number of pages. If the current page is greater than the total number of pages, we set it to the total number of pages. If the current page is less than 1, then we set it to 1.
We use the parse_url() function to get query parameters and save them as the $url_query variable. By using this function, we can easily update the page number query parameter without changing other parameters. For example, if the URL query is ?param1=value1¶m2=value2&page=2, in this case we want to keep all parameters but want to update page=2 only. Let's break down the functionality step by step.
- Get the total number of records in the employees table, which is important to determine the number of pagination pages.
- Set items to show per page in the
$items_per_pagevariable. - Calculate how many pages are needed in pagination navigation and store it as the
$total_pagesvariable. - Get the current page from the query parameter in the URL and store it as the
$current_pagevariable. - Save the current URL in a variable
$page_urland prepare previous ($previous_url) and next ($next_url) page links. - Calculate the offset or start limit for the query using the
($current_page - 1) * $items_per_pageequation. - Run a MySQLi query with an offset and limit clause to fetch paginated records from the table.
- Prepare an array of employees from the query results.
- Show employees in the table and finally show page links with a for loop using the
$total_pagesvariable.
index.php
<?php
include 'constants.php';
$db_connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die(mysqli_connect_error());
$total_rows_query = mysqli_query($db_connection, 'SELECT COUNT(*) AS total FROM employees');
$total_rows = (int)mysqli_fetch_array($total_rows_query)['total'];
$items_per_page = 3;
// Number of pages to show in pagination
$total_pages = ceil($total_rows / $items_per_page);
$current_page = min(max(1, $total_pages), max(1, intval($_GET['page'] ?? 1)));
// Page url & query parameters
$page_url = sprintf("%s/%s", BASE_URL, trim(parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH), '/'));
$url_query = (string)parse_url($_SERVER['REQUEST_URI'], PHP_URL_QUERY);
parse_str($url_query, $url_query);
// Prev page url using query parameter in url
$url_query['page'] = max(1, $current_page - 1);
$prev_url = sprintf('%s/?%s', $page_url, http_build_query($url_query));
// Next page url using query parameter in url
$url_query['page'] = min($total_pages, $current_page + 1);
$next_url = sprintf('%s/?%s', $page_url, http_build_query($url_query));
$limit_start = ($current_page - 1) * $items_per_page; // Where to start in database records
$stmt = mysqli_prepare($db_connection, 'SELECT * FROM employees LIMIT ?, ?');
mysqli_stmt_bind_param($stmt, 'ii', $limit_start, $items_per_page);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$employees = mysqli_fetch_all($result, MYSQLI_ASSOC);
mysqli_stmt_close($stmt);
mysqli_close($db_connection);
?>
<!DOCTYPE html>
<html>
<head>
<title>Implement Simple Pagination in PHP - Demo</title>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type"/>
<meta content="width=device-width, initial-scale=1, maximum-scale=1" name="viewport"/>
<link rel="stylesheet" href="css/style.css"/>
</head>
<body>
<section class="section py-4">
<div class="container">
<?php
if (!empty($employees)) {
foreach ($employees as $employee) { ?>
<div class="employee-row">
<div class="employee-image">
<img src="images/no-image.png" class="image-responsive" width="100" height="100" loading="lazy"
alt=""/>
</div>
<div class="employee-details">
<div><?= $employee['full_name']; ?></div>
<div><?= $employee['email']; ?></div>
<div><?= $employee['designation']; ?></div>
<div><?= $employee['gender']; ?></div>
</div>
</div>
<?php } ?>
<div class="pagination-container">
<ul class="pagination">
<li>
<a href="<?= $prev_url; ?>" class="page-link">Prev</a></li>
<?php
for ($i = 1; $i <= $total_pages; $i++) {
$url_query['page'] = $i;
$href = sprintf('%s/?%s', $page_url, http_build_query($url_query));
?>
<li class="<?= $i == $current_page ? 'active' : ''; ?>">
<a href="<?= $href; ?>" class="page-link"><?= $i; ?></a>
</li>
<?php
}
?>
<li>
<a href="<?= $next_url; ?>" class="page-link">Next</a>
</li>
</ul>
</div>
<?php
} else {
echo 'No Records Found';
}
?>
</div>
</section>
</body>
</html>
Add CSS Styles for Pagination Links
Add CSS styles for the whole HTML page and pagination links.
style.css
* {
box-sizing: border-box;
}
html,body {
margin: 0;
padding: 0;
}
body {
background-color: #f6f6f6;
font-family: "Segoe UI", "Roboto", "Helvetica", sans-serif;
font-size: 15px;
font-weight: normal;
font-style: normal;
}
a {
text-decoration: none;
color: #3778cd;
}
.py-4 {
padding-top: 1rem;
padding-bottom: 1rem;
}
.container {
width: 100%;
max-width: 1140px;
margin-right: auto;
margin-left: auto;
padding-right: 15px;
padding-left: 15px;
}
.pagination {
display: -webkit-box;
display: -ms-flexbox;
display: flex;
padding-left: 0;
list-style: none;
border-radius: 0.25rem;
}
.page-link {
position: relative;
display: block;
padding: 0.5rem 0.75rem;
margin-left: -1px;
line-height: 1.25;
color: #007bff;
background-color: #fff;
border: 1px solid #dee2e6;
}
.employee-row {
display: flex;
background: #fff;
color: #333;
padding: 10px;
margin-bottom: 15px;
box-shadow: 0 0 2px rgba(100,100,100,0.5);
}
.employee-image {
flex: 100px 0 0;
overflow:hidden;
}
.employee-image img {
max-width: 100%;
height: auto;
display: block;
}
.employee-details {
padding-left: 10px;
flex: 1 0 100px;
}We demonstrated how to split MySQL database records into smaller chunks and show them as pages in PHP with a simple PHP pagination example with working code snippets.