Pagination in PHP & MySQL
In this blog, you will learn how to create pagination in PHP. Pagination is very useful when you want to show limited data on a page and you want to show the remaining data on a button click, it is mostly used for blogs, eCommerce products, and web applications.
Let’s see the following steps to create pagination:
- Create a Database.
- Create a table.
- Create a connection file to connect the database.
- Create a PHP file where you want to show data.
Create a Database:
CREATE DATABASE php_pagination;
Create a Table:
CREATE TABLE `blogs` (
`id` int(11) NOT NULL,
`addeddate` date DEFAULT NULL,
`title` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`description` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`updatedat` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Create a Connection:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$db="php_pagination";
$conn = mysqli_connect($servername, $username, $password,$db);
?>
Create a index.php file or file where you want to print data:
<!DOCTYPE html>
<html lang="en">
<head>
<title>Pagination</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jquery.slim.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/umd/popper.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<div class="container">
<h2>Pagination in PHP</h2>
<table class="table table-striped">
<thead>
<tr>
<th>Added Date</th>
<th>Title</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr>
<td>John</td>
<td>Doe</td>
<td>[email protected]</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>
Now we have completed the steps, lets combine the steps to create a complete code.
Complete Code:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$db="php_pagination";
$conn = mysqli_connect($servername, $username, $password,$db);
$limit = 3;
if (isset($_GET["page"])) {
$page = $_GET["page"];
}
else{
$page=1;
};
$start_from = ($page-1) * $limit;
$result = mysqli_query($conn,"SELECT * FROM blogs ORDER BY addeddate DESC LIMIT $start_from, $limit");
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Pagination</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/jquery.slim.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/umd/popper.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<div class="container">
<h2>Pagination in PHP & Mysql</h2>
<table class="table table-striped">
<thead>
<tr>
<th>Added Date</th>
<th>Title</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<?php
$i=1;
while ($row = mysqli_fetch_array($result)) {
?>
<tr>
<td><?php echo $row['addeddate'];?></td>
<td><?php echo $row['title'];?></td>
<td><?php echo $row['description'];?></td>
</tr>
<?php $i++; } ?>
</tbody>
</table>
<?php
$result_db = mysqli_query($conn,"SELECT COUNT(id) FROM blogs");
$row_db = mysqli_fetch_row($result_db);
$total_records = $row_db[0];
$total_pages = ceil($total_records / $limit);
/* echo $total_pages; */
$pagLink = "<ul class='pagination'>";
for ($i=1; $i<=$total_pages; $i++) {
$pagLink .= "<li class='page-item'><a class='page-link' href='index.php?page=".$i."'>".$i."</a></li>";
}
echo $pagLink . "</ul>";
?>
</div>
</body>
</html>
Categories: web development php
