PHP Pagination with MySQL and Bootstrap Example

Html , Bootstrap 4 , Bootstrap , PHP

Nicesnippets

5028

05-01-2021


PHP Pagination with MySQL and Bootstrap Example

Hi Guys,

Today, I will learn you how to create pagination in php. We will show example of php pagination with mysql and bootstrap. you can easy to create pagination with mysql and bootstrap in php. We will make dynamic limit in pagination with the session, create prev, next feature, active class in pagination to display results fetched from MySQL database.

What is Pagination?

Pagination is a way of showing the data on multiple pages rather than putting them to a single page. Pagination helps to divide the records onto several pages, which makes the data more readable and understandable.

Pagination is a common task for PHP developers. MySQL helps the developer to create pagination by using LIMIT clause, which takes two arguments. The first argument as OFFSET and the second argument is number of records that will return from database.

Here, I will give you full example for simply pagination with mysql and bootstrap in php bellow.

Step 1: Create Table & Insert Data


In this setp, I will create table & insert data.

CREATE TABLE `students` (

`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,

`first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,

`last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,

`email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,

`birthdate` date NOT NULL,

`added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Insert the following data inside the students’ table.

INSERT INTO `students` (`id`, `first_name`, `last_name`, `email`, `birthdate`, `added`) VALUES

(1, 'Christian', 'Hackett', '[email protected]', '1983-12-30', '1992-02-05 13:21:46'),

(2, 'Percy', 'Blanda', 'to\'[email protected]', '2011-09-19', '1990-04-24 01:17:02'),

(3, 'Kennedi', 'Crona', '[email protected]', '2013-12-17', '1973-03-17 13:21:12'),

(4, 'Jordan', 'Hessel', '[email protected]', '1975-04-17', '1970-10-18 14:43:11'),

(5, 'Ila', 'Von', '[email protected]', '1989-10-04', '2004-08-15 06:25:33'),

(6, 'Caitlyn', 'Legros', '[email protected]', '2020-02-05', '1978-01-05 20:54:52'),

(7, 'Jace', 'Mills', '[email protected]', '2017-04-30', '1999-12-06 17:56:43'),

(8, 'Kiley', 'Hickle', '[email protected]', '1999-09-16', '2014-05-27 22:54:34'),

(9, 'Keshaun', 'Swift', '[email protected]', '1984-05-27', '1979-06-15 02:41:44'),

(10, 'Bernhard', 'Hudson', '[email protected]', '1996-09-30', '1987-10-15 19:29:03'),

(11, 'Brando', 'Maggio', '[email protected]', '2001-10-16', '1989-08-31 08:25:57'),

(12, 'Kariane', 'Dicki', '[email protected]', '2006-03-25', '2018-10-07 06:23:34'),

(13, 'Earnestine', 'Ankunding', '[email protected]', '1975-11-11', '2019-08-20 17:12:29'),

(14, 'Nayeli', 'Schiller', '[email protected]', '2005-01-28', '2008-02-28 19:42:52'),

(15, 'Tressie', 'Willms', '[email protected]', '1995-11-24', '2000-05-19 09:48:39'),

(16, 'Shaun', 'Walsh', '[email protected]', '1991-11-01', '1976-03-24 11:54:20'),

(17, 'Roosevelt', 'Leuschke', '[email protected]', '1984-12-16', '2004-10-01 00:21:22'),

(18, 'Bill', 'Farrell', '[email protected]', '1986-03-18', '1994-01-12 02:22:08'),

(19, 'Maurice', 'Johns', '[email protected]', '2000-12-07', '2004-07-16 02:59:16'),

(20, 'Taya', 'Towne', '[email protected]', '1972-01-14', '2018-04-19 22:00:33'),

(21, 'Ivah', 'Kuhlman', '[email protected]', '2003-10-30', '2004-08-28 08:01:06'),

(22, 'Virgie', 'Quitzon', '[email protected]', '1977-06-30', '1990-08-13 05:30:49'),

(23, 'Laurel', 'Lueilwitz', '[email protected]', '1973-03-10', '2006-06-24 15:01:07'),

(24, 'Colton', 'Wisoky', '[email protected]', '2004-03-13', '1972-04-13 10:39:32'),

(25, 'Frankie', 'Kutch', '[email protected]', '1983-07-16', '1993-03-27 06:29:23'),

(26, 'Noelia', 'Kertzmann', '[email protected]', '1990-10-18', '1989-02-02 16:52:51'),

(27, 'Aida', 'Durgan', '[email protected]', '1979-05-30', '1996-08-20 08:45:41'),

(28, 'Vesta', 'Stiedemann', 'jo\'[email protected]', '2019-03-18', '1977-11-04 12:13:54'),

(29, 'Emmy', 'Armstrong', '[email protected]', '1971-07-24', '1997-08-23 02:34:33'),

(30, 'Melany', 'Kris', '[email protected]', '1970-05-03', '1993-01-11 04:26:59'),

(31, 'Valentine', 'Boyle', '[email protected]', '1988-02-08', '2012-11-15 12:54:23'),

(32, 'Trisha', 'Gutmann', '[email protected]', '1992-07-21', '1989-10-25 21:52:17'),

(33, 'Angela', 'Stoltenberg', '[email protected]', '1973-08-15', '2008-11-21 16:16:02'),

(34, 'Dulce', 'Bartoletti', '[email protected]', '2011-04-03', '2015-10-07 05:27:01'),

(35, 'Haylie', 'Rohan', '[email protected]', '2003-07-15', '2005-05-10 00:13:04'),

(36, 'Daphney', 'Nikolaus', '[email protected]', '1978-02-19', '1984-02-12 08:32:02'),

(37, 'Gabriella', 'Wolf', '[email protected]', '2009-11-28', '2001-10-20 06:25:35'),

(38, 'Elvie', 'Pfannerstill', '[email protected]', '2014-08-14', '2015-10-19 13:48:05'),

(39, 'Elliot', 'Denesik', '[email protected]', '2005-02-28', '2015-01-29 07:09:30'),

(40, 'Jermaine', 'Cartwright', '[email protected]', '2013-07-05', '1970-03-26 02:34:32'),

(41, 'Herminio', 'Rosenbaum', '[email protected]', '1997-10-06', '2010-07-25 08:32:11'),

(42, 'Mateo', 'Raynor', '[email protected]', '2006-11-04', '2017-08-25 06:13:30'),

(43, 'Maymie', 'Runte', '[email protected]', '2000-06-19', '2018-06-01 05:42:58'),

(44, 'Demond', 'Skiles', '[email protected]', '1983-02-22', '2013-08-11 14:39:05'),

(45, 'Arvel', 'Jones', '[email protected]', '1975-03-20', '1974-10-04 10:44:12'),

(46, 'Donavon', 'Thiel', '[email protected]', '1994-12-25', '2019-05-05 13:08:57'),

(47, 'Aiyana', 'Ziemann', '[email protected]', '1987-02-18', '1982-12-16 09:38:25'),

(48, 'Gillian', 'Streich', '[email protected]', '1976-07-07', '1990-09-03 09:25:48'),

(49, 'Bryon', 'Roob', '[email protected]', '1979-06-21', '1979-03-28 01:58:17'),

(50, 'Wendy', 'McLaughlin', '[email protected]', '2018-06-06', '2002-10-11 21:50:33');

Step 2: Make Database Connection

Now this step I will cretae config/dbLink.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_pagination", $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();

}

?>

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.

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 students")->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

$students = $connection->query("SELECT * FROM students 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.

<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>

PHP Pagination Full Example

In this step ,The following is the final code for pagination example in PHP with previous and next buttons.

<?php

// Database

include('config/dbLink.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;

$students = $connection->query("SELECT * FROM students LIMIT $paginationStart, $limit")->fetchAll();

// Get total records

$sql = $connection->query("SELECT count(id) AS id FROM students")->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($students as $student): ?>

<tr>

<th scope="row"><?php echo $student['id']; ?></th>

<td><?php echo $student['first_name']; ?></td>

<td><?php echo $student['last_name']; ?></td>

<td><?php echo $student['email']; ?></td>

<td><?php echo $student['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>

It will help you....


Recommended Posts