How to do Pagination in Php & MySql ?

How to do Pagination in Php & MySql ?

In this tutorial we are going to create pagination using php and MySQL.  To do pagination in php you need to having a one database table that has at least 10-15 records for doing pagination using MySQL select query in php.


What is pagination ?
Pagination means displaying all data from database in to multiple pages. Because not good idea to display all the data in a single page. So, we can spit the result into multiple pages using php and MySQL.

Follow the below steps to create pagination with php and MySQL


1.    Get current page number

$current_page = isset($_GET['page']) && intval($_GET['page']) ? $_GET['page'] : 1;

2.    Write a query for getting total number of pages

$query = mysqli_query($conn, "SELECT * FROM table1 ");
$total = mysqli_num_rows($query);

3.    Formula for pagination

$items_per_page = 10; //Records to show per page
$num_of_pages = ceil($total / $items_per_page); // Number of pages to show in pagination
$page_url = 'Blog.php?page=';

4.    Set condition for getting page numbers

if ($current_page >= $num_of_pages) {
$next = $num_of_pages;
$current_page = $num_of_pages;
} else {
$next = $current_page + 1;
}
if ($current_page <= 1) {
$prev = 1;
$current_page = 1;
} else {
$prev = $current_page - 1;
}

5.    Define Variable for where to start record from database

$limit_start = ($current_page - 1) * $items_per_page; // Where to start in database records

6.    Write a query for getting data from database

$blog_query="select * from table1 LIMIT $limit_start,$items_per_page ";
$blog_data = mysqli_query($conn, $blog_query);

7.    Now, Display all data using while loop

<?php while($result= mysqli_fetch_assoc($blog_data)) { 
<ul>
<li><?php echo $result['col'];?></li>
</ul>
} ?>

8.    Now, Create pagination buttons

<nav aria-label="Page navigation example">
<ul >
<li ><a href="<?php echo $num_of_pages > 1 ? $page_url . $prev : 'javascript:;' ?>" > <i class="fa fa-angle-left"></i></a></li>
<?php
for($i = 1; $i <= $num_of_pages; $i++) {
$href = $i== $current_page ? 'javascript:;' : $page_url . $i; ?>
<li ><a href="<?php echo $href; ?>" class=" <?php echo $i== $current_page ? 'active' : ''; ?>"><?php echo $i; ?></a></li>
<?php }?>
<li ><a href="<?php echo $num_of_pages > 1 ? $page_url . $next : 'javascript:;' ?>" > <i class="fa fa-angle-right"></i></a></li>
</ul>
</nav>

9.    Let’s combine all code

<?php
$current_page = isset($_GET['page']) && intval($_GET['page']) ? $_GET['page'] : 1; //get current page number
$query = mysqli_query($conn, "SELECT * FROM table1 ");
$total = mysqli_num_rows($query); //get total number of records
$items_per_page = 10; //Records to show per page
$num_of_pages = ceil($total / $items_per_page); // Number of pages to show in pagination
$page_url = 'Blog.php?page='; //url of page to load next page
if ($current_page >= $num_of_pages) {
$next = $num_of_pages;
$current_page = $num_of_pages;
} else {
$next = $current_page + 1;
}
if ($current_page <= 1) {
$prev = 1;
$current_page = 1;
} else {
$prev = $current_page - 1;
}
$limit_start = ($current_page - 1) * $items_per_page; // Where to start in database records
$query_1="select * from table1 LIMIT $limit_start,$items_per_page "; //getting all data from databa se
$data_1 = mysqli_query($conn, $query_1);
while($result= mysqli_fetch_assoc($data_1)) { ?>
<ul>
<li><?php echo $result['col'];?></li>
</ul>
<?php } ?>
<nav aria-label="Page navigation example">
<ul >
<li ><a href="<?php echo $num_of_pages > 1 ? $page_url . $prev : 'javascript:;' ?>" > <i class="fa fa-angle-left"></i></a></li>
<?php
for($i = 1; $i <= $num_of_pages; $i++) {
$href = $i== $current_page ? 'javascript:;' : $page_url . $i; ?>
<li ><a href="<?php echo $href; ?>" class=" <?php echo $i== $current_page ? 'active' : ''; ?>"><?php echo $i; ?></a></li>
<?php }?>
<li ><a href="<?php echo $num_of_pages > 1 ? $page_url . $next : 'javascript:;' ?>" > <i class="fa fa-angle-right"></i></a></li>
</ul>
</nav>

That’s all. If you find this article is helpful don’t forget to share with your friends. Please help us by doing comment using comment box. Thank you!

Also Read:

How to use postman for api ??

Post Comments(0)

Leave a reply