This tutorial explains how to create pagination in PHP 8 and MySQL using the Bootstrap 4 Pagination UI component.Also, learn how to set the dynamic limit in pagination with the session, create prev, next feature, active class in pagination to display results fetched from MySQL database.
Pagination Features:
- Previous button in pagination to go back.
- Next button in pagination to go forward.
- Disable the previous button when reached the first item in pagination.
- Disable the next button when reached the last item in pagination.
- Add an active class in the pagination to set the active state.
- Set a dynamic limit for pagination items via the select dropdown.
- Set session for pagination limit.
What is Pagination?
Pagination is a graphical way of displaying a large set of data in smaller pieces, and It makes the data analysis process easy for the users.
If i try to make it simpler, so think about the search results you get when you write a search query in the Google search toolbar.
You see the numerical list with previous and next links at the very bottom of your screen.
Google breaks down the million results in a smaller data set to show you the results in a more precise manner.
It makes you quickly browse the information that you were looking for. This is known as the pagination.
Create PHP Project
Based on your XAMPP or MAMP webserver get inside the htdocs folder and create php-demo folder, inside here we will create all our project related files.
Create Table & Insert Data
Create the `authors` table in the MySQL database.
CREATE TABLE `authors` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`first_name` varchar(50) COLLATE utf8_general_ci NOT NULL,
`last_name` varchar(50) COLLATE utf8_general_ci NOT NULL,
`email` varchar(100) COLLATE utf8_general_ci NOT NULL,
`birthdate` date NOT NULL,
`added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;Insert the following data inside the authors’ table.
INSERT INTO `authors` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES
(1, 'Christian', 'Hackett', 'suzanne41@example.com', '1983-12-30', '1992-02-05 13:21:46'),
(2, 'Percy', 'Blanda', 'to\'keefe@example.org', '2011-09-19', '1990-04-24 01:17:02'),
(3, 'Kennedi', 'Crona', 'xmorissette@example.com', '2013-12-17', '1973-03-17 13:21:12'),
(4, 'Jordan', 'Hessel', 'lucio73@example.com', '1975-04-17', '1970-10-18 14:43:11'),
(5, 'Ila', 'Von', 'bkohler@example.net', '1989-10-04', '2004-08-15 06:25:33'),
(6, 'Caitlyn', 'Legros', 'gusikowski.alycia@example.com', '2020-02-05', '1978-01-05 20:54:52'),
(7, 'Jace', 'Mills', 'mante.claud@example.org', '2017-04-30', '1999-12-06 17:56:43'),
(8, 'Kiley', 'Hickle', 'megane34@example.net', '1999-09-16', '2014-05-27 22:54:34'),
(9, 'Keshaun', 'Swift', 'ahickle@example.com', '1984-05-27', '1979-06-15 02:41:44'),
(10, 'Bernhard', 'Hudson', 'ramiro46@example.com', '1996-09-30', '1987-10-15 19:29:03'),
(11, 'Brando', 'Maggio', 'katarina90@example.org', '2001-10-16', '1989-08-31 08:25:57'),
(12, 'Kariane', 'Dicki', 'hwilliamson@example.net', '2006-03-25', '2018-10-07 06:23:34'),
(13, 'Earnestine', 'Ankunding', 'nwindler@example.org', '1975-11-11', '2019-08-20 17:12:29'),
(14, 'Nayeli', 'Schiller', 'camden.kemmer@example.net', '2005-01-28', '2008-02-28 19:42:52'),
(15, 'Tressie', 'Willms', 'randerson@example.com', '1995-11-24', '2000-05-19 09:48:39'),
(16, 'Shaun', 'Walsh', 'howell.brenna@example.net', '1991-11-01', '1976-03-24 11:54:20'),
(17, 'Roosevelt', 'Leuschke', 'janiya.kub@example.com', '1984-12-16', '2004-10-01 00:21:22'),
(18, 'Bill', 'Farrell', 'bins.moses@example.net', '1986-03-18', '1994-01-12 02:22:08'),
(19, 'Maurice', 'Johns', 'katelyn.friesen@example.org', '2000-12-07', '2004-07-16 02:59:16'),
(20, 'Taya', 'Towne', 'vbauch@example.net', '1972-01-14', '2018-04-19 22:00:33'),
(21, 'Ivah', 'Kuhlman', 'vswaniawski@example.org', '2003-10-30', '2004-08-28 08:01:06'),
(22, 'Virgie', 'Quitzon', 'terrell.ratke@example.net', '1977-06-30', '1990-08-13 05:30:49'),
(23, 'Laurel', 'Lueilwitz', 'karen02@example.com', '1973-03-10', '2006-06-24 15:01:07'),
(24, 'Colton', 'Wisoky', 'ivory40@example.com', '2004-03-13', '1972-04-13 10:39:32'),
(25, 'Frankie', 'Kutch', 'schuster.adrianna@example.com', '1983-07-16', '1993-03-27 06:29:23'),
(26, 'Noelia', 'Kertzmann', 'dubuque.blanca@example.org', '1990-10-18', '1989-02-02 16:52:51'),
(27, 'Aida', 'Durgan', 'brendan05@example.org', '1979-05-30', '1996-08-20 08:45:41'),
(28, 'Vesta', 'Stiedemann', 'jo\'kon@example.net', '2019-03-18', '1977-11-04 12:13:54'),
(29, 'Emmy', 'Armstrong', 'schuster.adrienne@example.org', '1971-07-24', '1997-08-23 02:34:33'),
(30, 'Melany', 'Kris', 'antonio.towne@example.net', '1970-05-03', '1993-01-11 04:26:59'),
(31, 'Valentine', 'Boyle', 'swift.joana@example.net', '1988-02-08', '2012-11-15 12:54:23'),
(32, 'Trisha', 'Gutmann', 'jdickinson@example.net', '1992-07-21', '1989-10-25 21:52:17'),
(33, 'Angela', 'Stoltenberg', 'walter.leta@example.com', '1973-08-15', '2008-11-21 16:16:02'),
(34, 'Dulce', 'Bartoletti', 'mosciski.nolan@example.com', '2011-04-03', '2015-10-07 05:27:01'),
(35, 'Haylie', 'Rohan', 'edna.maggio@example.net', '2003-07-15', '2005-05-10 00:13:04'),
(36, 'Daphney', 'Nikolaus', 'tdibbert@example.org', '1978-02-19', '1984-02-12 08:32:02'),
(37, 'Gabriella', 'Wolf', 'egutmann@example.org', '2009-11-28', '2001-10-20 06:25:35'),
(38, 'Elvie', 'Pfannerstill', 'aorn@example.org', '2014-08-14', '2015-10-19 13:48:05'),
(39, 'Elliot', 'Denesik', 'borer.tierra@example.net', '2005-02-28', '2015-01-29 07:09:30'),
(40, 'Jermaine', 'Cartwright', 'lhane@example.org', '2013-07-05', '1970-03-26 02:34:32'),
(41, 'Herminio', 'Rosenbaum', 'shanahan.gilda@example.com', '1997-10-06', '2010-07-25 08:32:11'),
(42, 'Mateo', 'Raynor', 'esmeralda.yost@example.com', '2006-11-04', '2017-08-25 06:13:30'),
(43, 'Maymie', 'Runte', 'kwhite@example.com', '2000-06-19', '2018-06-01 05:42:58'),
(44, 'Demond', 'Skiles', 'schinner.westley@example.com', '1983-02-22', '2013-08-11 14:39:05'),
(45, 'Arvel', 'Jones', 'udietrich@example.net', '1975-03-20', '1974-10-04 10:44:12'),
(46, 'Donavon', 'Thiel', 'smitham.keven@example.org', '1994-12-25', '2019-05-05 13:08:57'),
(47, 'Aiyana', 'Ziemann', 'katlyn.shields@example.com', '1987-02-18', '1982-12-16 09:38:25'),
(48, 'Gillian', 'Streich', 'zmertz@example.com', '1976-07-07', '1990-09-03 09:25:48'),
(49, 'Bryon', 'Roob', 'rosanna03@example.com', '1979-06-21', '1979-03-28 01:58:17'),
(50, 'Wendy', 'McLaughlin', 'katelyn.howell@example.com', '2018-06-06', '2002-10-11 21:50:33');Make Database Connection
Open config/db.php file and place the following code to connect PHP project with MySQL database.
<?php
$hostname = "localhost";
$username = "root";
$password = "";
try {
$connection = new PDO("mysql:host=$hostname;dbname=php_crud", $username, $password);
// set the PDO error mode to exception
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo "Database connection failed: " . $e->getMessage();
}
?>We are using the Bootstrap library to create the table and pagination layout to display the Authors’ results. However, you can use custom CSS to build pagination and table layout.
Calculate Total Pages with Dynamic Limit
The $limit variable sets the dynamic limit for displaying the result via the pagination and select dropdown.
We wrap the results limit in session so if the user selects the limit from the dropdown, and the selected limit won’t go away on browser refresh.
Grab the total id using the SELECT count() method to find out the total records in the table.
The ceil() function rounds the number up to the nearest integer.
// Dynamic limit
$limit = isset($_SESSION['records-limit']) ? $_SESSION['records-limit'] : 5;
// Get total records
$sql = $connection->query("SELECT count(id) AS id FROM authors")->fetchAll();
$allRecrods = $sql[0]['id'];
// Calculate total pages
$totoalPages = ceil($allRecrods / $limit);PHP Pagination & SQL Query
MySQL gives a LIMIT clause that is used to define the number of records to return.
The LIMIT clause allows displaying multi-page results via pagination with SQL and is very helpful with large tables.
To get the pagination number, we will define the page parameter later with pagination.
To get the offset or paginationStart we deduct the current page from 1 and divide it by the page limit.
$limit = isset($_SESSION['records-limit']) ? $_SESSION['records-limit'] : 5;
// Current pagination page number
$page = (isset($_GET['page']) && is_numeric($_GET['page']) ) ? $_GET['page'] : 1;
// Offset
$paginationStart = ($page - 1) * $limit;
// Limit query
$authors = $connection->query("SELECT * FROM authors LIMIT $paginationStart, $limit")->fetchAll();Pagination Implementation with PHP
We have defined the formula and necessary variables, now we create the pagination and display the result based on data limitation.
A user can go backward and forward using the pagination, see the active class for the current page.
<!-- Pagination -->
<nav aria-label="Page navigation example mt-5">
<ul class="pagination justify-content-center">
<li class="page-item <?php if($page <= 1){ echo 'disabled'; } ?>">
<a class="page-link"
href="<?php if($page <= 1){ echo '#'; } else { echo "?page=" . $prev; } ?>">Previous</a>
</li>
<?php for($i = 1; $i <= $totoalPages; $i++ ): ?>
<li class="page-item <?php if($page == $i) {echo 'active'; } ?>">
<a class="page-link" href="index.php?page=<?= $i; ?>"> <?= $i; ?> </a>
</li>
<?php endfor; ?>
<li class="page-item <?php if($page >= $totoalPages) { echo 'disabled'; } ?>">
<a class="page-link"
href="<?php if($page >= $totoalPages){ echo '#'; } else {echo "?page=". $next; } ?>">Next</a>
</li>
</ul>
</nav>Set Dynamic Records Limit
First import the jQuery CDN link, we need to get the value from the select dropdown.
Run a loop and pass the values that we want to use for setting up the records limit.
We are taking the values from the session and set the same value as a selected.
<form action="index.php" method="post">
<select name="records-limit" id="records-limit" class="custom-select">
<option disabled selected>Records Limit</option>
<?php foreach([5,7,10,12] as $limit) : ?>
<option
<?php if(isset($_SESSION['records-limit']) && $_SESSION['records-limit'] == $limit) echo 'selected'; ?>
value="<?= $limit; ?>">
<?= $limit; ?>
</option>
<?php endforeach; ?>
</select>
</form>
<!-- jQuery + Bootstrap JS -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script>
$(document).ready(function () {
$('#records-limit').change(function () {
$('form').submit();
})
});
</script>Pagination in PHP Code Example
The following is the final code for pagination example in PHP with previous and next buttons.
You have to create the index.php file at the root of php-demo folder:
<?php
// Database
include('config/db.php');
// Set session
session_start();
if(isset($_POST['records-limit'])){
$_SESSION['records-limit'] = $_POST['records-limit'];
}
$limit = isset($_SESSION['records-limit']) ? $_SESSION['records-limit'] : 5;
$page = (isset($_GET['page']) && is_numeric($_GET['page']) ) ? $_GET['page'] : 1;
$paginationStart = ($page - 1) * $limit;
$authors = $connection->query("SELECT * FROM authors LIMIT $paginationStart, $limit")->fetchAll();
// Get total records
$sql = $connection->query("SELECT count(id) AS id FROM authors")->fetchAll();
$allRecrods = $sql[0]['id'];
// Calculate total pages
$totoalPages = ceil($allRecrods / $limit);
// Prev + Next
$prev = $page - 1;
$next = $page + 1;
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/css/bootstrap.min.css">
<title>PHP Pagination Example</title>
<style>
.container {
max-width: 1000px
}
.custom-select {
max-width: 150px
}
</style>
</head>
<body>
<div class="container mt-5">
<h2 class="text-center mb-5">Simple PHP Pagination Demo</h2>
<!-- Select dropdown -->
<div class="d-flex flex-row-reverse bd-highlight mb-3">
<form action="index.php" method="post">
<select name="records-limit" id="records-limit" class="custom-select">
<option disabled selected>Records Limit</option>
<?php foreach([5,7,10,12] as $limit) : ?>
<option
<?php if(isset($_SESSION['records-limit']) && $_SESSION['records-limit'] == $limit) echo 'selected'; ?>
value="<?= $limit; ?>">
<?= $limit; ?>
</option>
<?php endforeach; ?>
</select>
</form>
</div>
<!-- Datatable -->
<table class="table table-bordered mb-5">
<thead>
<tr class="table-success">
<th scope="col">#</th>
<th scope="col">First</th>
<th scope="col">Last</th>
<th scope="col">Email</th>
<th scope="col">DOB</th>
</tr>
</thead>
<tbody>
<?php foreach($authors as $author): ?>
<tr>
<th scope="row"><?php echo $author['id']; ?></th>
<td><?php echo $author['first_name']; ?></td>
<td><?php echo $author['last_name']; ?></td>
<td><?php echo $author['email']; ?></td>
<td><?php echo $author['birthdate']; ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<!-- Pagination -->
<nav aria-label="Page navigation example mt-5">
<ul class="pagination justify-content-center">
<li class="page-item <?php if($page <= 1){ echo 'disabled'; } ?>">
<a class="page-link"
href="<?php if($page <= 1){ echo '#'; } else { echo "?page=" . $prev; } ?>">Previous</a>
</li>
<?php for($i = 1; $i <= $totoalPages; $i++ ): ?>
<li class="page-item <?php if($page == $i) {echo 'active'; } ?>">
<a class="page-link" href="index.php?page=<?= $i; ?>"> <?= $i; ?> </a>
</li>
<?php endfor; ?>
<li class="page-item <?php if($page >= $totoalPages) { echo 'disabled'; } ?>">
<a class="page-link"
href="<?php if($page >= $totoalPages){ echo '#'; } else {echo "?page=". $next; } ?>">Next</a>
</li>
</ul>
</nav>
</div>
<!-- jQuery + Bootstrap JS -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script>
$(document).ready(function () {
$('#records-limit').change(function () {
$('form').submit();
})
});
</script>
</body>
</html>View App on Browser
We are ready to run the PHP application for that you need to put following URL on the browser and hit enter.
http://localhost/php-demo/Conclusion
We learned to divide the broad set of data into multiple pages using the pagination.
I hope you liked this tutorial, don’t forget to share it with others.
You can also download the full code of this project from GitHub.