Simple Pagination in PHP
This post demonstrates how to paginate MySQL data in PHP. This is going to be very simple PHP pagination implemented in PHP. We will retrieve records from our employees table and paginate them.
What is Pagination in PHP and Why Use It?
Pagination in PHP is an approach used to split large number of data records into small chunks to display limited information to user. When large number of data records is retrieved from database it can result in slow page load time, PHP pagination helps to avoid this slow page load time. For example you might have a huge number of products, users, or articles. Instead of showing them all on one page we can show limited number of records and show them as different pages with navigation to each page providing a better user experience.
How to Implement Pagination in PHP?
These code snippets in this post will help you to implement pagination in PHP in simple and easy steps with working demo example. We need to follow some steps logically as below:
- Get total number of records in database table.
- Set the number how many items will be displayed per page.
- Then use total number of records and items per page to calculate how many pages you needs.
- Fetch the exact number of records number of records from database table providing the offset and limit of records based on current page number and items per page limit.
- Then create a loop and iterate the exact number of times as total number of pages providing the navigation to other pages.
Files we are going to need for this pagination are:
- employees.sql: Contains database records for employees table.
- constants.php: Contains the required constants for database connection and queries.
- index.php: Will be retrieving employees records from database and paginate 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 which you will need to import it will
create the employees table and insert dummy records for our pagination.
employees.sql
DROP TABLE IF EXISTS `employees`; CREATE TABLE IF NOT EXISTS `employees` ( `employee_id` int(11) NOT NULL AUTO_INCREMENT, `fullname` text, `email` varchar(255) DEFAULT NULL, `gender` tinytext, `designation` varchar(100) DEFAULT NULL, `image` text, PRIMARY KEY (`employee_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ; INSERT INTO `employees` (`employee_id`, `fullname`, `email`, `gender`, `designation`, `image`) VALUES (1, 'Art Venere', '[email protected]', 'Male', 'Salesman', NULL), (2, 'Anderson Thomas', '[email protected]', 'Male', 'Salesman', NULL), (3, 'Darell Garcia', '[email protected]', 'Male', 'Data Entry Operator', NULL), (4, 'Alisha Watson', '[email protected]', 'Female', 'Data Entry Operator', NULL), (5, 'Catherine Wilson', '[email protected]', 'Female', 'Data Entry Operator', NULL), (6, 'Andrew Willis', '[email protected]', 'Male', 'Human Resource Manager', NULL), (7, 'Jenna Gibson', '[email protected]', 'Female', 'Customer Relationship Officer', NULL), (8, 'Stephen Dorf', '[email protected]', 'Female', 'Store Keeper', NULL), (9, 'Angela Parsons', '[email protected]', 'Female', 'Accountant', NULL), (10, 'George Orwell', '[email protected]', 'Male', 'Accounts Manager', NULL), (11, 'Graciela Motley', '[email protected]', 'Female', 'Assistant Manager', NULL);
Add Constants for Database Connection
The constants.php will be containing site URL and database connection constants that we will be using in our index.php.
constants.php
<?php
define('BASE_URL', 'https://' . $_SERVER['SERVER_NAME']);
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 Links for Pages
This script is used for pagination in PHP. We connect to our database and show paginated results on same page.
We gracefully handle the current page number if user enters an invalid page number in URL like -1 or any page number greater than total number of pages. If current page is greater than total number of pages we set it to total number of pages. If current page is less than 1 then we set it to 1.
We use parse_url() function to get query parameters and save it in $url_query. Using this function we can easily update page number query parameter without changing other parameters. For example if URL query is ?param1=value1¶m2=value2&page=2 in this case we want to keep all parameters but want to update only page=2. Lets break it down the functionality step by step.
- Get the total number of records in employees table which is important to determine the number of pages in pagination.
- Set items to show per page in a variable $items_per_page.
- Calculate how many pages are needed in pagination navigation and store it in a variable $total_pages.
- Get the current page from query parameter in URL and store it in a variable $current_page.
- Save the current URL in a variable $page_url and prepare previous ($previous_url) and next ($next_url) page links.
- Calculate the offset or start limit for query using formula ($current_page - 1) * $items_per_page.
- Run MySQLi query with offset and limit clause to fetch paginated records from table.
- Prepare an array of employees from query results.
- Show employees in table and finally show page links with for loop using the variable $items_per_page.
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 = mysqli_query($db_connection, 'SELECT FOUND_ROWS() FROM employees')->num_rows;
$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
$query = mysqli_query($db_connection, "SELECT * FROM employees LIMIT {$limit_start},{$items_per_page}");
while ($row = mysqli_fetch_assoc($query)) {
$employees[] = $row;
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Simple Pagination in PHP - Demo</title>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type"/>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<div class="container">
<?php
if (!empty($employees)) {
foreach ($employees as $employee) {
$image = empty($employee['employee_image']) ? 'no-image.png' : $employee['employee_image'];
?>
<div class="employee-row">
<div class="employee-image">
<img src="images/<?=$image ?>" 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>
</body>
</html>
Add CSS Styles for Pagination Links
Add necessary CSS styles for whole HTML page and pagination navigation.
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;
}
.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;
}