Hi Guys,
In this blog, I will learn you PHP pagination PHP is mostly used to store and display data from a database. Pagination can be done with ajax, but here this is done with non-ajax. In this tutorial, we will learn the pagination in PHP with MySQL. Let's take a brief review about pagination with an example -
It is possible that a SQL SELECT query may returns millions of records back. It is not a good idea to display all records on a single page. A large list of records on a single page may take so much time to load the page and also consume time to find specific data. This may cause (leads to) the confusion in user's mind. Therefore, divide these records over several pages according to the user requirement.
So, what can we do to distribute these large number of records in number of pages? The method of distributing a single list into multiple pages is known as Pagination. Paging refers to showing your query result on multiple pages instead of a single page.
What is Pagination?
Pagination is a way of showing the data on multiple pages rather than putting them to a single page. Pagination avails to divide the records onto several pages, which makes the data more readable and understandable.
Pagination is a prevalent task for PHP developers. MySQL avails the developer to engender pagination by utilizing LIMIT clause, which takes two arguments. The first argument as OFFSET and the second argument is number of records that will return from database.
Let's look at some advantages and disadvantages of using pagination concept in PHP -
Advantages of Pagination
-
Pagination is very useful in astronomically immense scale projects because it makes the webwork more professional. Not only more professional, but it additionally makes the webpage work much more expeditious, precise, and efficient.
-
With the help of pagination, we can save the loading time of a page by dividing the data on various pages. It saves us from loading a lot of information at once.
For Example - A webpage with 1000 images will take more time to load images than the 50 images on each webpage.
This means that thousands of images need thousands of HTTP requests, which would make the page unresponsive. This problem is resolved by limiting the amount of data with the help of pagination using LIMIT clause.
-
The use of pagination improves the user experience, advertising revenue, and decrease the loading time of the page.
Disadvantages of Pagination
While there are some powerful advantages of pagination, but still many developers avoid to use it. Along with some powerful advantages, there are few disadvantages of pagination as well, which are as follows:
-
Pagination itself is a big overhead in PHP, which is one of the disadvantages of pagination. It is completely an overhead as it is an external feature that can be implemented to the cost of extraneous Markup, Styling, and logic. A small dataset often ignored to use pagination.
-
Pagination may cause of low page rank on search engine because when a page is away from the home page and requires several clicks, it usually does not get a high page rank.
-
It also limits the number links, social shares, total number of results to visible on the webpage, and anchor text that a page receives when the information is split over several pages.
Implementation of Pagination with PHP and MySQL
In order to implement the pagination, we require an astronomically immense dataset to apply pagination to it. Ergo, first we require to engender a database and table. After that, provide the records in the table and commence coding to engender pagination. So that the data fetched from the database can be split over several pages.
Here we will introduce two examples of pagination. First example is a simple and basic example of pagination creation with no CSS, whereas in second example, we will create pagination in attractive way using CSS and bootstrap. You can see the output for both. Below are the steps given for pagination creation;
Simple steps to create pagination -
-
Create a database and table. Provide a list of records into the table.
-
Connect with the MySQL database.
-
Create the pagination link to split the data on multiple pages and add them to bottom of the table.
-
Fetch data from the database and display it to the multiple pages.
Follow the below step one by one and create simple pagination.
Example
The below example is another example of pagination in which we used CSS along with HTML to make webpage view more attractive. CSS makes the webpage more creative and attractive. On the other hand, MySQL stores the data in database. So, you can learn pagination much better.
We have written whole code in a single file except database connectivity. Therefore, we will create two files, i.e., connection.php and index1.php. Save both the files in .php extension. In the example below, you will learn to create pagination more creative and attractive.
File: connection.php
Database Connectivity
You can write database connectivity code in the same file as well as also keep it separate into another file and include it to your required PHP file. Code for database connection-
<?php
$conn = mysqli_connect('localhost', 'root', 'root');
if (! $conn) {
die("Connection failed" . mysqli_connect_error());
}
else {
mysqli_select_db($conn, 'php_curd');
}
?>
Fetch data and display on webpage
As we have created dataset, now we need to fetch and display it to various webpages. The below code is used to retrieve the data from database and display on the webpages that are divided accordingly.
Fetch data
After establishing the database connection in "connection.php" file, we just need to import it into our code using require_once keyword. We will explicitly define the number of records per page to show.
<?php
require_once "connection.php";
$per_page_record = 4; // Number of entries to show in a page.
// Look for a GET variable page if not found default is 1.
if (isset($_GET["page"])) {
$page = $_GET["page"];
}
else {
$page=1;
}
//determine the sql LIMIT starting number for the results on the displaying page
$start_from = ($page-1) * $per_page_record;
$query = "SELECT * FROM product LIMIT $start_from, $per_page_record";
$rs_result = mysqli_query ($conn, $query);
?>
Display data
This section is very simple. In this section, we iterate the loop over the records that we fetched and display each record stored in columns of the table.
<?php
while ($row = mysqli_fetch_array($rs_result)) {
// Display each field of the records.
?>
<tr>
<td><?php echo $row["id"]; ?></td>
<td><?php echo $row["product_name"]; ?></td>
<td><?php echo $row["product_price"]; ?></td>
<td><?php echo $row["product_category"]; ?></td>
<td><?php echo $row["product_details"]; ?></td>
<td><?php echo $row["product_stock"]; ?></td>
</tr>
<?php
};
?>
Pagination Link creation
Now the most important code is pagination link creation. So, we will create the Previous, Next, and numeric links for pagination and add them to bottom of the table.
<?php
if($page>=2) {
echo "<a href='index1.php?page=".($page-1)."'> Prev </a>";
}
for ($i=1; $i<=$total_pages; $i++) {
if ($i == $page) {
$pagLink .= "<a class = 'active' href='index1.php?page="
.$i."'>".$i." </a>";
}
else {
$pagLink .= "<a href='index1.php?page=".$i."'>
".$i." </a>";
}
};
echo $pagLink;
if($page<$total_pages){
echo "<a href='index1.php?page=".($page+1)."'> Next </a>";
}
?>
Code for Random Moment
In case when the number of pages is too much, this code helps us for random moment. By entering the page number in the input field, a user can directly move to that page. This code is written in JavaScript.
<?php
function go2Page()
{
var page = document.getElementById("page").value;
page = ((page><?php echo $total_pages; ?>)?<?php echo $total_pages; ?>:((page<1)?1:page));
window.location.href = 'index1.php?page='+page;
}
?>
Final Code
File: Index1.php
<?php
<html>
<head>
<title>How To Create Pagination In PHP and Mysql - itwebtuts</title>
<link rel="stylesheet"
href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<style>
table {
border-collapse: collapse;
}
.inline{
display: inline-block;
float: right;
margin: 20px 0px;
}
input, button{
height: 34px;
}
.pagination {
display: inline-block;
}
.pagination a {
font-weight:bold;
font-size:18px;
color: black;
float: left;
padding: 8px 16px;
text-decoration: none;
border:1px solid black;
}
.pagination a.active {
background-color: skyblue ;
}
.pagination a:hover:not(.active) {
background-color: pink;
}
</style>
</head>
<body>
<center>
<?php
// Import the file where we defined the connection to Database.
require_once "connection.php";
$per_page_record = 4; // Number of entries to show in a page.
// Look for a GET variable page if not found default is 1.
if (isset($_GET["page"])) {
$page = $_GET["page"];
}
else {
$page=1;
}
$start_from = ($page-1) * $per_page_record;
$query = "SELECT * FROM product LIMIT $start_from, $per_page_record";
$rs_result = mysqli_query ($conn, $query);
?>
<div class="container">
<br>
<div>
<h1>Pagination Simple Example - itwebtuts</h1>
<table class="table table-striped table-condensed
table-bordered">
<thead>
<tr>
<th width="10%">ID</th>
<th>Name</th>
<th>price</th>
<th>category</th>
<th>details</th>
<th>stock</th>
</tr>
</thead>
<tbody>
<?php
while ($row = mysqli_fetch_array($rs_result)) {
// Display each field of the records.
?>
<tr>
<td><?php echo $row["id"]; ?></td>
<td><?php echo $row["product_name"]; ?></td>
<td><?php echo $row["product_price"]; ?></td>
<td><?php echo $row["product_category"]; ?></td>
<td><?php echo $row["product_details"]; ?></td>
<td><?php echo $row["product_stock"]; ?></td>
</tr>
<?php
};
?>
</tbody>
</table>
<div class="pagination">
<?php
$query = "SELECT COUNT(*) FROM product";
$rs_result = mysqli_query($conn, $query);
$row = mysqli_fetch_row($rs_result);
$total_records = $row[0];
echo "</br>";
// Number of pages required.
$total_pages = ceil($total_records / $per_page_record);
$pagLink = "";
if($page>=2){
echo "<a href='index1.php?page=".($page-1)."'> Prev </a>";
}
for ($i=1; $i<=$total_pages; $i++) {
if ($i == $page) {
$pagLink .= "<a class = 'active' href='index1.php?page="
.$i."'>".$i." </a>";
}
else {
$pagLink .= "<a href='index1.php?page=".$i."'>
".$i." </a>";
}
};
echo $pagLink;
if($page<$total_pages){
echo "<a href='index1.php?page=".($page+1)."'> Next </a>";
}
?>
</div>
<div class="inline">
<input id="page" type="number" min="1" max="<?php echo $total_pages?>"
placeholder="<?php echo $page."/".$total_pages; ?>" required>
<button class="btn btn-success" onClick="go2Page();">Go</button>
</div>
</div>
</div>
</center>
<script>
function go2Page()
{
var page = document.getElementById("page").value;
page = ((page><?php echo $total_pages; ?>)?<?php echo $total_pages; ?>:((page<1)?1:page));
window.location.href = 'index1.php?page='+page;
}
</script>
</body>
</html>
?>
I Will help you.
