Implement Simple Pagination in PHP
This post demonstrates how to paginate MySQL data in PHP. This is going to be very simple PHP pagination example. 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 Create Simple Pagination in PHP with MySQL?
The code snippets in this post will help you to implement pagination in PHP in simple and easy steps with working demo example. Steps to be followed in order to paginate MySQL data in PHP:
- 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 need.
- Fetch the exact number of records from database table providing the offset and limit of records based on current page number and items per page limit.
- Add a for loop and run the iteration for number of times equal to total number of pages. Each page will have a link except for current page, previous page if current page is 1 and next page if current page is equal to total number of pages i.e. current page is last page.
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 we will need to import. It will
create the employees table and insert sample 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 Page Links
Core PHP pagination script that connects to database, calculate pages and displays page links. 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 as $url_query variable. 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 page=2 only. 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
$items_per_pagevariable. - Calculate how many pages are needed in pagination navigation and store it as
$total_pagesvariable. - Get the current page from query parameter in URL and store it as
$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 query using
($current_page - 1) * $items_per_pageequation. - 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
$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);
?>
<!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) {
$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>
</section>
</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;
}
.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;
}