How to Create Pagination With Php and Mysql
Pagination breaks down large lists. So the page view is good and loads faster. Pagination is used in almost all web projects. Here is a simple example for you. We will make our application using Php, Mysql, Jquery and Bootstrap technologies.
First, we need to create database and tables.
CREATE TABLE IF NOT EXISTS `employee` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`employee_name` varchar(70) NOT NULL,
`employee_salary` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `employee` (`id`, `employee_name`, `employee_salary`) VALUES
(1, 'Jon Jones', '90000'),
(2, 'Khabib Nurmagomedov', '78000'),
(3, 'Stipe Miocic ', '65000'),
(4, 'Dustin Poirier', '85000'),
(5, 'Justin Gaethje', '30000'),
(6, 'Max Holloway', '28000'),
(7, 'Petr Yan ', '16000'),
(8, 'Conor McGregor ', '52000'),
(9, 'Tony Ferguson', '78000'),
(10, 'Robert Whittaker ', '42000'),
(11, 'Nate Diaz', '48000'),
(12, 'Jorge Masvidal', '84000');
I used the names of the ufc fighters in the example. It is the sport I follow with love. 😄
Let's use the following code to create a MySQL database connection
connect.php
<?php
/* Database connection start */
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "pagination";
$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
?>
container.php dosyası
</head>
<body class="">
<div role="navigation" class="navbar navbar-default navbar-static-top">
<div class="container">
<div class="navbar-header">
<button data-target=".navbar-collapse" data-toggle="collapse" class="navbar-toggle" type="button">
<span class="sr-only"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a href="#" class="navbar-brand">PAGINATION</a>
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li class="active"><a href="#">LİST</a></li>
</ul>
</div><!--/.nav-collapse -->
</div>
</div>
<div class="container" style="min-height:500px;">
<div class=''>
</div>
Including the bootstrap and jquery libraries with our header.php file
header.php
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="bootstrap.min.css">
<link rel="stylesheet" href="bootstrap-theme.min.css">
<script src="jquery.min.js"></script>
<script src="bootstrap.min.js"></script>
And finally our index page
index.php
<?php
include('header.php');
?>
<title>Simple Pagination with PHP and MySQL</title>
<?php include('container.php');?>
<div class="container">
<h2>Simple Pagination with PHP and MySQL onurtaskiran.com</h2>
<?php
include_once("connect.php");
$showRecordPerPage = 5;
if(isset($_GET['page']) && !empty($_GET['page'])){
$currentPage = $_GET['page'];
}else{
$currentPage = 1;
}
$startFrom = ($currentPage * $showRecordPerPage) - $showRecordPerPage;
$totalEmpSQL = "SELECT * FROM employee";
$allEmpResult = mysqli_query($conn, $totalEmpSQL);
$totalEmployee = mysqli_num_rows($allEmpResult);
$lastPage = ceil($totalEmployee/$showRecordPerPage);
$firstPage = 1;
$nextPage = $currentPage + 1;
$previousPage = $currentPage - 1;
$empSQL = "SELECT id,employee_name, employee_salary
FROM `employee` LIMIT $startFrom, $showRecordPerPage";
$empResult = mysqli_query($conn, $empSQL);
?>
<table class="table ">
<thead>
<tr>
<th>EmpID</th>
<th>Name</th>
<th>Salary</th>
</tr>
</thead>
<tbody>
<?php
while($emp = mysqli_fetch_assoc($empResult)){
?>
<tr>
<th scope="row"><?php echo $emp['id']; ?></th>
<td><?php echo $emp['employee_name']; ?></td>
<td><?php echo $emp['employee_salary']; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
<nav aria-label="Page navigation">
<ul class="pagination">
<?php if($currentPage != $firstPage) { ?>
<li class="page-item">
<a class="page-link" href="?page=<?php echo $firstPage ?>" tabindex="-1" aria-label="Previous">
<span aria-hidden="true">First</span>
</a>
</li>
<?php } ?>
<?php if($currentPage >= 2) { ?>
<li class="page-item"><a class="page-link" href="?page=<?php echo $previousPage ?>"><?php echo $previousPage ?></a></li>
<?php } ?>
<li class="page-item active"><a class="page-link" href="?page=<?php echo $currentPage ?>"><?php echo $currentPage ?></a></li>
<?php if($currentPage != $lastPage) { ?>
<li class="page-item"><a class="page-link" href="?page=<?php echo $nextPage ?>"><?php echo $nextPage ?></a></li>
<li class="page-item">
<a class="page-link" href="?page=<?php echo $lastPage ?>" aria-label="Next">
<span aria-hidden="true">Last</span>
</a>
</li>
<?php } ?>
</ul>
</nav>
</div>
</div>
</body>
</html>
Result Page 1
Page 
That is all. good codings. Good luck.
