Simple Pagination in PHP
Today we will learn how to paginate mysql data in php. This is going to be very simple php pagination. We will retrieve recrods from our employees table and paginate them.
Files we are going to need for this pagination are:
- employees.sql: Contains database records for employees table
- index.php: Will be retrieving employees records from database and paginate them
- style.css: Will contain the styles for our index.php and pagination.
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);
Then 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
Then we create index.php which is main file where we are retrieving employees records from database table and paginate them. We first include our constants.php and then create a connection to database.
First we will get the number of records in employees table which is important to determine the number of pages in pagination. $current_page and $items_per_page will be used to determine the offset point in database on a single paginated page. For example if we are currently on page 2 and we are showing 3 employees per page then the start limit will be ($current_page - 1) * $items_per_page = 3
We then 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.
Next we save the page url in a variable $page_url and we use parse_url() to get query parameters and save it in $url_query. The reason I used this method is if there are other parameters in url they shouldn't be changed or skipped and it will be easier to update only page number this way. 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. Then we create the url of next page and previous page. At last we retrieve records of current page from database and paginate them.
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>
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;
}