Pagination with PHP and MySQL

In this tutorial we are going to learn how to create Pagination with PHP and MYSQL. It
is probably done in the case of large data sets, where the resultant records are in thousands or
more. In this case the result is divided into multiple pages.

What is Pagination?

  • Pagination means displaying the results on multiple pages instead of displaying them on a single page.
  • When the records returned are in large numbers it is difficult to display on one page. And it may take too much time to load the page.
  • It is best practice to split the records into multiple pages so it can be easily read & viewed by the user.
  • With the help of MySQL’s LIMIT clause we can create a pagination feature, which takes two arguments. First argument as OFFSET and second argument number of records returned from the database.

Steps to create Pagination in PHP

STEP 1:-Create database and table.

Database:-

Create a database with the following command or you can create with phpMyAdmin
CREATE DATABASE studentdb;

STEP 2:-Connect with Database.

Create php file name student.php and write the following code to make a connection with the database.

<?php
	// $conn = mysqli_connect("host",dbusername,dbpassword,dbname);
	// In my case dbpassword is empty
	$con = mysqli_connect("localhost","root","","studentdb");
	if (mysqli_connect_errno()){
		echo "Connection Failed:- " mysqli_connect_error();
		die();
	}
?>

STEP 3:-By using $_GET we get the current page number.

If (isset($_GET[‘page_no’])){
	$page_no = $_GET[‘page_no’];
}
else{
	$page_no = 1;
}

STEP 4:-We can limit the number of records displayed per page by changing the value of this variable $record_per_page. Here we will display 10 records per page, You can show more than 10 records as welll

$record_per_page = 10;

STEP 5:-Set the offset value and also calculate the next and previous page number.

$start = ($page_no - 1) * $record_per_page;
$prev = $page_no - 1;
$next = $page_no + 1;

STEP 6:-Fetch the total number of pages.

$query = mysqli_query($con, "SELECT COUNT(*) FROM student_table");
$row_count = mysqli_fetch_array($query);
$total_rows = $row_count[0];
$total_pages = ceil($total_rows/$ record_per_page);

STEP 7:- Now we use LIMIT clause and OFFSET to fetch limited number of records.

$sql = mysqli_query($con, “SELECT * FROM student_table LIMIT $start, $ record_per_page”);
while($row = mysqli_fetch_array($sql)){
    echo "<tr>
 <td>".$row['Id']."</td>
 <td>".$row['Name']."</td>
 <td>".$row['Age']."</td>
 <td>".$row['Field']."</td>
 </tr>";
        }
mysqli_close($con);

Table will be created from this. Also create table header before writing php script. Include bootstrap in head section to include CSS

<table class="table table-striped table-bordered">
<thead>
<tr>
<th style='width:50px;'>ID</th>
<th style='width:150px;'>Name</th>
<th style='width:50px;'>Age</th>
<th style='width:150px;'>Field</th>
</tr>
</thead>
<tbody>
<!--
    //We will right php script here
-->
</tbody>
</table>

STEP 8:-Create Pagination buttons

This is the important part where you will create buttons i.e. First, Next, Previous, and Last page buttons.

<ul class="student">
	<?php 
	if($page_no > 1){
		// Show first only if page number is greater than 1
		echo "<li><a href='?page_no=1'>First</a></li>";
	} 
	?>
	// If page number = 1 then you can not select previous
	<li<?php if($page_no <= 1){ echo "class='disabled'"; } ?>>
		<a <?php if($page_no > 1){ echo "href='?page_no=$prev'";} ?>>Previous</a>
	</li>
	//If its last page then can not click next
	<li <?php if($page_no >= $total_pages){ echo "class='disabled'"; } ?>>
		<a <?php if($page_no < $total_pages) { echo "href='?page_no=$next'"; }  ?>>Next</a>
	</li>
	// If its not last page then show last
	<?php 
		if($page_no < $total_pages){
			echo "<li><a href='?page_no=$total_pages'>Last</a></li>";
		} 
	?>
</ul>

This button works in following manner:-

If current page number is greater than 1 then it will display the First button otherwise it will be hidden. Similarly last button, if current page number is equal to the last page number, then it will hide last button.

In between next and previous buttons are created, so if current page is equal or less than 1 then it will disable the previous button. Similarly with next button, if current number is equal to the last page number then it will hide it too.