Simple pagination in PHP with MySqli example.
Hello all, In this tutorial, I am going to share how we can implement Simple pagination in PHP with MySqli example.
But first of all, let’s see what actually is pagination and why we need it in our application.
Assume that you are having thousands of record in your database tables which you need to show on a page.
Now you can run “SELECT” query and show that record in a table or list structure on your page.
But as the record size is large your page would take a time to load, as the script needs to select all the record “AT ONCE”, in the single SELECT query.
This would become an inconvenience to the end user, who may wait until the page fully loads.
To overcome these difficulties we use an approach or technique called “Pagination”. In pagination, we actually divide the number of records to be shown in different parts and called them as pages.
It means at a time we show the only particular number of records say on the first page and same number or NEXT records on next pages respectively.
So, to achieve this pagination we have to perform certain steps. Let’s see those steps to implement simple pagination in PHP with MySqli example.
Step 1 – First find out the count of how many records present in a table which you have to show.
Step 2 – Decide the number of records you want to show on a single page.
Step 3 – Divide the count of record with the no. of records to show, the result will be your total number of pages required to show the complete record.
Step 4 – In this step, we will use a special SELECT query with the limit and offset parameter to fetch the particular amount of records only at a single time. More details in step implementation.
Now let’s see the above steps in detail and actual implementation of pagination.
I am using a simple example to explain here.
Let’s see the steps in detail to implement Simple pagination in PHP with MySqli example
Step 1 –
In this step, connect the database with the application using MySQLi. and run the SELECT COUNT query to get the count of records present in a database table, of which records you want to show.
I am using “users” table in my database. Store the count in a variable says named “$total_records”. Look at the code snippet below.
<?php
$con = mysqli_connect('localhost','root','','acems');
if(mysqli_connect_errno())
{
echo "Failed connection";
}
$query = "select count(*) from users";
$result = mysqli_query($con,$query);
$countResult = mysqli_fetch_row($result);
$total_records = $countResult[o];
?>
Step 2 –
Now let’s assume we want to show 5 records on a single page. Store 5 in the variable — $limit = 5
Steps 3 –
In this step divide the $total_records by $limit and store the result in a variable – $total_pages. This will give the number of pages required to show the whole record. Follow below code.
<?php
$con = mysqli_connect('localhost','root','','acems');
if(mysqli_connect_errno())
{
echo "Failed connection";
}
$query = "select count(*) from users";
$result = mysqli_query($con,$query);
$countResult = mysqli_fetch_row($result);
$total_records = $countResult[o];
$limit = 5; //No of records to show on single page
$total_pages = $total_records / $limit; //Total pages to show all records
?>
Step 4 –
In this step, we use a special SELECT query which contains “limit” and “offset” values. The query would be like this – “select * from users limit $limit offset $offset“.
As you see in above query after limit we used a $limit variable and after offset, we used another variable $offset.
Here this select query will give the no. of records with the limit we have given, in this example limit is 5, which we have stored above.
And offset is the position of a record from where the records would get fetched. By default, the query considers the first record while selecting.
Here the term OFFSET will start from the value ZERO. It means if we set the offset to zero then only select starts from the first record.
If we change the offset lets say 2, then the selection of record will start from the row number 3 =>(2 + 1 = 3).
Now let’s assume a query “select * from users limit 5 offset 0“. Here I have considered limit 5 and offset 0. This query will fetch only five records starting from the first row.
“select * from users limit 5 offset 5” => This query will fetch 5 records from the row sixth row. (5 + 1 = 6).
Similarly, we can fetch all the records in the chunk of 5s by changing the offset values as per the need.
We will use the power of offset combining with the page numbers and limit to fetch the records on every page.
Our page numbers will start from one. Let’s create a variable $current_page and assign a default value as 1 in it.
Now let’s create the page numbers on page and hyperlink then to the same page with the query string passing the page numbers which we will collect in a variable $current_page in next steps. See the code below.
<?php
$con = mysqli_connect('localhost','root','','acems');
if(mysqli_connect_errno())
{
echo "Failed connection";
}
$query = "select count(*) from users";
$result = mysqli_query($con,$query);
$countResult = mysqli_fetch_row($result);
$total_records = $countResult[o];
$limit = 5; //No of records to show on single page
$total_pages = $total_records / $limit; //Total pages to show all records
<ul class="pagination">
<?php
if($current_page == 1)
{
echo "<li><a href='#'><<</a></li>";
}
else
{
$prev = $current_page - 1;
echo "<li><a href='http://localhost/phptuto/php/pagination.php?page=$prev'><<</a></li>";
}
?>
<?php
for($i=1;$i<=$total_pages;$i++)
{
echo "<li><a href='http://localhost/phptuto/php/pagination.php?page=$i'>$i</a></li>";
}
?>
<?php
if($current_page == $total_pages)
{
echo "<li><a href='#'>>></a></li>";
}
else
{
$next = $current_page + 1;
echo "<li><a href='http://localhost/phptuto/php/pagination.php?page=$next'>>></a></li>";
}
?>
</ul>
?>
The above code will give you the pagination buttons at the bottom.
Now after clicking the pagination button collects the page number in $_GET and store it in a $current_page variable.
Then we have to calculate the offset value using $offset = ($current_page – 1) * $limit; Using this code the offset value would get calculated as per the next page. After this run the SELECT query and show the result in HTML table.
See the final code snippet below.
<link rel="stylesheet" href="bootstrap-3.2.0-distcssbootstrap.min.css" />
<?php
$con = mysqli_connect('localhost','root','','acems');
if(mysqli_connect_errno())
{
echo "Failed connection";
}
$query = "select count(*) from users";
$result = mysqli_query($con,$query);
$countResult = mysqli_fetch_row($result);
print_r($countResult);
$total_records = $countResult[o];
$limit = 5;
$total_pages = $total_records / $limit;
$current_page = 1;
if(isset($_GET['page']) && !empty($_GET['page']))
{
$current_page = $_GET['page'];
}
if($current_page == 1)
{
$offset = 0;
}
else
{
$offset = ($current_page - 1) * $limit;
}
$query = "select * from users limit $limit offset $offset";
$result = mysqli_query($con,$query);
?>
<div class="container">
<table class="table table-striped">
<thead>
<tr>
<th>Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
</tr>
</thead>
<tbody>
<?php
while($data = mysqli_fetch_assoc($result))
{
?>
<tr>
<td><?php echo $data['id']?></td>
<td><?php echo $data['first_name']?></td>
<td><?php echo $data['last_name']?></td>
<td><?php echo $data['email']?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<ul class="pagination">
<?php
if($current_page == 1)
{
echo "<li><a href='#'><<</a></li>";
}
else
{
$prev = $current_page - 1;
echo "<li><a href='http://localhost/phptuto/php/pagination.php?page=$prev'><<</a></li>";
}
?>
<?php
for($i=1;$i<=$total_pages;$i++)
{
echo "<li><a href='http://localhost/phptuto/php/pagination.php?page=$i'>$i</a></li>";
}
?>
<?php
if($current_page == $total_pages)
{
echo "<li><a href='#'>>></a></li>";
}
else
{
$next = $current_page + 1;
echo "<li><a href='http://localhost/phptuto/php/pagination.php?page=$next'>>></a></li>";
}
?>
</ul>
</div>
I hope you would understand the steps and procedure in the above tutorial for simple pagination in PHP with MySqli example.
Subscribe here by email for more tutorials:
