In this post we will learn pagination example in PHP and MySQL. Pagination is very useful in case of huge amount of data. When we select data from huge amount of data we should avoid to display all data at once. So in such a case pagination play an important role .
Pagination means displaying all the data into multiple pages instead of showing them on single page.
MySQL Limit and Offset clause helps us to create pagination in PHP.
Below is the simple step to create pagination in PHP and MySQL.
Step 1 : Create Table In MySQL.
CREATE TABLE test.user ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, email VARCHAR(50), mobno bigint(10) )
CREATE TABLE test.user ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, email VARCHAR(50), mobno bigint(10) )
CREATE TABLE test.user ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, email VARCHAR(50), mobno bigint(10) )
Step 2 : Insert Some Data Into Table.
INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');
INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');
INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');INSERT INTO `user` (`id`, `name`, `email`, `mobno`) VALUES (NULL, 'gfgfd', 'test@gmail.com', '65464877');
Step 3 : Create PHP File Pagination.php
PHP MySQL Pagination example
<?php
$servername='localhost';
$username='root';
$password='';
$dbname = "test";
$conn=mysqli_connect($servername,$username,$password,"$dbname");
if(!$conn){
die('Could not Connect My Sql:' .mysql_error());
}
$limit = 5;
if (isset($_GET["page"])) {
$page = $_GET["page"];
}
else{
$page=1;
};
$start_from = ($page-1) * $limit;
$result = mysqli_query($conn,"SELECT * FROM user ORDER BY id ASC LIMIT $start_from, $limit");
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>PHP MySQL Pagination example</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body>
<table class="table table-bordered table-striped">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Mob No</th>
</tr>
<thead>
<tbody>
<?php
while ($data = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $data["id"]; ?></td>
<td><?php echo $data["name"]; ?></td>
<td><?php echo $data["email"]; ?></td>
<td><?php echo $data["mobno"]; ?></td>
</tr>
<?php
};
?>
</tbody>
</table>
<?php
$total_rows = mysqli_query($conn,"SELECT COUNT(id) FROM user");
$row_db = mysqli_fetch_row($total_rows);
$total_records = $row_db[0];
$total_pages = ceil($total_records / $limit);
$pagLink = "<ul class='pagination'>";
for ($i=1; $i<=$total_pages; $i++) {
if($i==$page){
$pagLink .= "<li class='page-item active'><a class='page-link' href='pagination.php?page=".$i."'>".$i."</a></li>";
}else{
$pagLink .= "<li class='page-item'><a class='page-link' href='pagination.php?page=".$i."'>".$i."</a></li>";
}
}
echo $pagLink . "</ul>";
?>
<?php
$servername='localhost';
$username='root';
$password='';
$dbname = "test";
$conn=mysqli_connect($servername,$username,$password,"$dbname");
if(!$conn){
die('Could not Connect My Sql:' .mysql_error());
}
$limit = 5;
if (isset($_GET["page"])) {
$page = $_GET["page"];
}
else{
$page=1;
};
$start_from = ($page-1) * $limit;
$result = mysqli_query($conn,"SELECT * FROM user ORDER BY id ASC LIMIT $start_from, $limit");
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>PHP MySQL Pagination example</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body>
<table class="table table-bordered table-striped">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Mob No</th>
</tr>
<thead>
<tbody>
<?php
while ($data = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $data["id"]; ?></td>
<td><?php echo $data["name"]; ?></td>
<td><?php echo $data["email"]; ?></td>
<td><?php echo $data["mobno"]; ?></td>
</tr>
<?php
};
?>
</tbody>
</table>
<?php
$total_rows = mysqli_query($conn,"SELECT COUNT(id) FROM user");
$row_db = mysqli_fetch_row($total_rows);
$total_records = $row_db[0];
$total_pages = ceil($total_records / $limit);
$pagLink = "<ul class='pagination'>";
for ($i=1; $i<=$total_pages; $i++) {
if($i==$page){
$pagLink .= "<li class='page-item active'><a class='page-link' href='pagination.php?page=".$i."'>".$i."</a></li>";
}else{
$pagLink .= "<li class='page-item'><a class='page-link' href='pagination.php?page=".$i."'>".$i."</a></li>";
}
}
echo $pagLink . "</ul>";
?>
<?php
$servername='localhost';
$username='root';
$password='';
$dbname = "test";
$conn=mysqli_connect($servername,$username,$password,"$dbname");
if(!$conn){
die('Could not Connect My Sql:' .mysql_error());
}
$limit = 5;
if (isset($_GET["page"])) {
$page = $_GET["page"];
}
else{
$page=1;
};
$start_from = ($page-1) * $limit;
$result = mysqli_query($conn,"SELECT * FROM user ORDER BY id ASC LIMIT $start_from, $limit");
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>PHP MySQL Pagination example</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body>
<table class="table table-bordered table-striped">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Mob No</th>
</tr>
<thead>
<tbody>
<?php
while ($data = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $data["id"]; ?></td>
<td><?php echo $data["name"]; ?></td>
<td><?php echo $data["email"]; ?></td>
<td><?php echo $data["mobno"]; ?></td>
</tr>
<?php
};
?>
</tbody>
</table>
<?php
$total_rows = mysqli_query($conn,"SELECT COUNT(id) FROM user");
$row_db = mysqli_fetch_row($total_rows);
$total_records = $row_db[0];
$total_pages = ceil($total_records / $limit);
$pagLink = "<ul class='pagination'>";
for ($i=1; $i<=$total_pages; $i++) {
if($i==$page){
$pagLink .= "<li class='page-item active'><a class='page-link' href='pagination.php?page=".$i."'>".$i."</a></li>";
}else{
$pagLink .= "<li class='page-item'><a class='page-link' href='pagination.php?page=".$i."'>".$i."</a></li>";
}
}
echo $pagLink . "</ul>";
?> Note : Change Your Database Credential i.e. Databse name , username and password.
