In this tutorial, we are going to create pagination with PHP and MySql. It is probably possible that your SQL SELECT query may return result into thousand and millions of records. And it is obviously not a good idea to display all those results on one page. So we can split this result into multiple pages.
What is Pagination?
Paging means displaying all your fetched results in multiple pages instead of showing them all on one page. It makes that page so long and would take so much time to load.
How to create Pagination with PHP and MySql
MySQL’s LIMIT clause helps us to create a pagination feature. It uses two arguments First argument as
OFFSET and second argument the number of records which will be returned from the database.
Follow these simple steps to create pagination in PHP –
1. Get the current page number
This code will get the current page number with the help of
$_GET Array. Note that if it is not present it will set the default page number to 1.
if (isset($_GET['pageno'])) {
$pageno = $_GET['pageno'];
} else {
$pageno = 1;
}
2. The formula for php pagination
You can always manage the number of records to be displayed in a page by changing the value of
$no_of_records_per_page variable.
$no_of_records_per_page = 10;
$offset = ($pageno-1) * $no_of_records_per_page;
3. Get the number of total number of pages
$total_pages_sql = "SELECT COUNT(*) FROM table";
$result = mysqli_query($conn,$total_pages_sql);
$total_rows = mysqli_fetch_array($result)[0];
$total_pages = ceil($total_rows / $no_of_records_per_page);
4. Constructing the SQL Query for pagination
$sql = "SELECT * FROM table LIMIT $offset, $no_of_records_per_page";
5. Pagination buttons
These Buttons are served to users as
Next Page & Previous Page, so that they can easily navigate through you pages. Here I am using bootstrap’s pagination button, you can use your own buttons if you want.
<ul class="pagination">
<li><a href="?pageno=1">First</a></li>
<li class="<?php if($pageno <= 1){ echo 'disabled'; } ?>">
<a href="<?php if($pageno <= 1){ echo '#'; } else { echo "?pageno=".($pageno - 1); } ?>">Prev</a>
</li>
<li class="<?php if($pageno >= $total_pages){ echo 'disabled'; } ?>">
<a href="<?php if($pageno >= $total_pages){ echo '#'; } else { echo "?pageno=".($pageno + 1); } ?>">Next</a>
</li>
<li><a href="?pageno=<?php echo $total_pages; ?>">Last</a></li>
</ul>
6. Let’s assemble all the codes in one page
<html>
<head>
<title>Pagination</title>
<!-- Bootstrap CDN -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<?php
if (isset($_GET['pageno'])) {
$pageno = $_GET['pageno'];
} else {
$pageno = 1;
}
$no_of_records_per_page = 10;
$offset = ($pageno-1) * $no_of_records_per_page;
$conn=mysqli_connect("localhost","my_user","my_password","my_db");
// Check connection
if (mysqli_connect_errno()){
echo "Failed to connect to MySQL: " . mysqli_connect_error();
die();
}
$total_pages_sql = "SELECT COUNT(*) FROM table";
$result = mysqli_query($conn,$total_pages_sql);
$total_rows = mysqli_fetch_array($result)[0];
$total_pages = ceil($total_rows / $no_of_records_per_page);
$sql = "SELECT * FROM table LIMIT $offset, $no_of_records_per_page";
$res_data = mysqli_query($conn,$sql);
while($row = mysqli_fetch_array($res_data)){
//here goes the data
}
mysqli_close($conn);
?>
<ul class="pagination">
<li><a href="?pageno=1">First</a></li>
<li class="<?php if($pageno <= 1){ echo 'disabled'; } ?>">
<a href="<?php if($pageno <= 1){ echo '#'; } else { echo "?pageno=".($pageno - 1); } ?>">Prev</a>
</li>
<li class="<?php if($pageno >= $total_pages){ echo 'disabled'; } ?>">
<a href="<?php if($pageno >= $total_pages){ echo '#'; } else { echo "?pageno=".($pageno + 1); } ?>">Next</a>
</li>
<li><a href="?pageno=<?php echo $total_pages; ?>">Last</a></li>
</ul>
</body>
</html>Source: https://www.myprogrammingtutorials.com/create-pagination-with-php-and-mysql.html
