How to Create Pagination in PHP and MYSQL

by

In this tutorial, I will learn you How to create dynamic pagination in PHP. Even I have shared the best & general source code within a custom function. This function will help you to create multiple paginations in multiple web pages by writing the given code at once.  This code is very simple to learn and integrate into the project.

Before Starting Coding, You should know the following basic information for your better understanding.

Pagination in PHP?

Pagination is the group of multiple web pages. It divides the large records into a limited number of records and displays them into more than one web page. Even It can quickly lead the records one by one page.

Pagination is also a commonly used feature in almost various web applications. Because this feature makes it very simple to view the large records. Even you can see it on my blog Home page.

Why Need PHP Pagination?

In most cases, Developers have to display a large number of records on the web page. But they can’t display those records on a single web page. Because Those records will take much time to load. Even those may fail to load together. So, It is not a good way to display all the records at once on only one page.

Hence, Developers need PHP pagination to remove the loading issue and easily display the records in a proper way.

Pagination Formula in PHP

This is the general formula to use for creating pagination in PHP.

You can use the following formula to find the total number of pagination pages.

$totalPages = ceil($totalRecords / $totalRecordsPerPage);

You can use the following formula to find the total number of previous records. It helps to display the next record by using the MySQL limit.

$totalPreviousRecords = ($currentPage-1) * $totalRecordsPerPage;

Example

Suppose that you have a total of 50 records are saved in the database. you have to display 5 records per page. Now you can calculate the total number of pages, the total number of previous records & the next 5 records using the above formula. (Suppose the current page number is 3).

From above example, We get $totalRecords=50; $totalRecordsPerPage=5; $currentPage= 3; using formula

$totalPages = ceil($totalRecords / $totalRecordsPerPage); $totalPages= ceil(50/5);

Output  is 10

Hence, Total of 10 pagination pages will be created using formula

$totalPreviousRecords = ($currentPage-1) * $totalRecordsPerPage;

$totalPreviousRecords = (3-1)*5;

output is 10

Hence, total number of previous recods is 10

In this way, you can get next 5 records using the following query

SELECT * From tableName limit $totalPreviousRecords, $totalRecordsPerPage;

So, This query will be like SELECT * From tableName limit 10, 5;

Hence, the above query will select next 5 records except previous 10 records

Dynamic Pagination in PHP with Next and Previous

Here, you will learn to create pagination with a blog post example. This example will load 5 records at once. But you need not worry, I have created the general code to implement it. So, you can easily update it according to your requirement.
pagination in php

Basic Configuration

Before going to the next step, you should configure the following basic things

  • Create the following folder structure
myproject/
   |__database.php
   |__pagination-page.php
   |__pagination-script.php
   |

 

  • Create a MySQL Database & table

I have created codingstatus database & blogs table. You can create it according to your project requirement.

  • Insert Records into MySQL Table

I will create a pagination to insert 65 records in a blogs. You can insert it according to your project requirement.

Read Also  How to Insert Data Using Ajax in PHP and MySQL

  • Now write the script of the next steps pagination.php. all the script is written within user-defined functions. do the following script in it.
    • Write  require('database.php')  to connect MySQL database.
    • Initialize connection variable by using $db=$connection

1. Connect PHP to MYSQL Database

First of all, you have to connect PHP to the MySQL database. After that start coding to create a pagination

File Name – database.php

<?php

$hostname     = "localhost"; 
$username     = "root"; 
$password     = ""; 
$databasename = "codingstatus.com";

// Create connection
$connection = mysqli_connect($hostname, $username, $password,$databasename);
// Check connection
if (!$connection) {
    die("Unable to Connect database: " . mysqli_connect_error());
}
?>

2. Create Pagination in PHP

  • current_page() is declared to get current page number
  • validate($value) is declared to validate current page number
  • pagination_records($totalRecordsPerPage,$tableName) is declared to fetch record for the pagination.You can call the above function and pass the total number of records per page & table name. This the general function. you can fetch any table records by passing the table name as a parameter.
  • pagination_records_counter() is declared to create a Serial Number of Pagination Records. You can call the above function where you want to display pagination records. Even you have to pass the total number of records per page.
  • previous_page() is declared to Create the Previous Pagination in PHP
  • next_page($totalPages) is declared to Create the Next Pagination in PHP
  • pagination_numbers($totalPages) is declared to Create Pagination Serial Numbers
  • pagination($totalRecordsPerPage,$tableName) is declared to create a pagination

File Name – pagination-script.php

<?php

require('database.php');

$db=$connection; // enter your connection variable

// current page number
function current_page(){
  if (isset($_GET['page']) && $_GET['page']!="") {
      $currentPage = validate($_GET['page']);
  } else {
      $currentPage = 1;
  }
 return $currentPage;
}

// validate current page number
function validate($value) {
  $value = trim($value);
  $value = stripslashes($value);
  $value = htmlspecialchars($value);
  return $value;
}

// fetching padination data
function pagination_records($totalRecordsPerPage,$tableName){

   global $db;
   $currentPage=current_page();
   $totalPreviousRecords = ($currentPage-1) * $totalRecordsPerPage; 

   $query = $db->prepare("SELECT * FROM ".$tableName." LIMIT ?, ?");
   $query->bind_param('ii',$totalPreviousRecords,$totalRecordsPerPage); 
   $query->execute();
   $result=$query->get_result();

   if($result->num_rows>0){
    $row= $result->fetch_all(MYSQLI_ASSOC);
    return $row;  
        
    }else{
    return $row=[];
   }
}

// serial number of pagination content
function pagination_records_counter($totalRecordsPerPage){
 
    $currentPage=current_page();
    $totalPreviousRecords=($currentPage-1)*$totalRecordsPerPage;
    $dataCounter=$totalPreviousRecords + 1;
    return $dataCounter;
}

// back to previous page
function previous_page(){

 $currentPage=current_page();
 $previousPage = $currentPage - 1;
 if($currentPage > 1){
   $previous="<a href='?page=".$previousPage."'>Previous</a>";
   return $previous;
 }  
}

// go to next page
function next_page($totalPages){

 $currentPage=current_page();
 $nextPage = $currentPage + 1;
 if($currentPage < $totalPages) {
  $next="<a href='?page=".$nextPage."'
>Next</a>";
  return $next;
}}

// diplaying total pagination numbers
function pagination_numbers($totalPages){
    
$currentPage=current_page();

    $adjacents = "2";
$second_last = $totalPages - 1; // total pages minus 1

 $pagelink='';
 if ($totalPages <= 5){   
 for ($counter = 1; $counter <= $totalPages; $counter++){
 if ($counter == $currentPage) {
  $pagelink.= "<a class='active'>".$counter."</a>"; 
 }else{
  $pagelink.= "<a href='?page=".$counter."'>".$counter."</a>";
  }
 }
}elseif ($totalPages > 5){
   if($currentPage <= 4) { 
    for ($counter = 1; $counter < 8; $counter++){ 
     if ($counter == $currentPage) {
        $pagelink.= "<a class='active' href='?page=".$counter."'>".$counter."</a>";
      }else{
           $pagelink.= "<a href='?page=".$counter."'>".$counter."</a>";
      }
    }
 $pagelink.= "<a>...</a>";
 $pagelink.= "<a href='?page=".$second_last."'>".$second_last."</a>";
 $pagelink.= "<a href='?page=".$totalPages."'>".$totalPages."</a>";
}elseif($currentPage > 4 && $currentPage < $totalPages - 4) { 
 $pagelink.= "<a href='?=1'>1</a>";
 $pagelink.= "<a href='?page=2'>2</a>";
 $pagelink.= "<a>...</a>";
for (
     $counter = $currentPage - $adjacents;
     $counter <= $currentPage + $adjacents;
     $counter++
     ) { 
     if ($counter == $currentPage) {
       $pagelink.= "<a class='active'>".$counter."</a>"; 
     }else{
        $pagelink.= "<a href='?page=".$counter."'>".$counter."</a>";
     }                  
}
 $pagelink.= "<a>...</a>";
 $pagelink.= "<a href='?page=".$second_last."'>".$second_last."</a>";
 $pagelink.= "<a href='?page=".$totalPages."'>".$totalPages."</a>";
}else {
 $pagelink.= "<a href='?page=1'>1</a>";
 $pagelink.= "<a href='?page=2'>2</a>";
 $pagelink.= "<a>...</a>";
for (
     $counter = $totalPages - 6;
     $counter <= $totalPages;
     $counter++
     ) {
     if ($counter == $currentPage) {
       $pagelink.= "<a class='active'>".$counter."</a>"; 
       }else{
        $pagelink.= "<a href='?page=".$counter."'>".$counter."</a>";
       }                   
}}}
return $pagelink;
}

// final script to create pagination
function pagination($totalRecordsPerPage,$tableName){

 $currentPage=current_page();
 global $db; 
 $query ="SELECT * FROM ".$tableName;
 $result=$db->query($query);
 
 $totalRecords=$result->num_rows;
 $totalPages = ceil($totalRecords / $totalRecordsPerPage);

 $pagination='';
 $pagination.=previous_page();
 $pagination.=pagination_numbers($totalPages);
 $pagination.=next_page($totalPages);

  return $pagination;
}
?>

 

10. Display Pagination with 5 records

To display pagination with 5 records, you have to write the code according to the following steps –

  • First of all, Include pagination-script.php
  • Assign 5 to the $totalRecordsPerPage and ‘blogs’ to the $tableName
  • Also, assign pagination_records($totalRecordsPerPage,$tableName) to the $paginationData, pagination_records_counter($totalRecordsPerPage) to the $sn and pagination($totalRecordsPerPage,$tableName) to the $pagination
  • Create a div with class=”pagination-content”  and apply foreach loop with $paginationData to print pagination record within it.
  • Also, create another div with class=”pagination” and print $pagination to display the pagination
  • Write CSS code to design the user-friendly pagination

File Name – pagination-page.php

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Pagination in PHP</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <style type="text/css">
.pagination-content{
 width:60%;
 text-align: justify;
 padding:20px;
}
.pagination{
 padding:20px;
}
.pagination a.active{
 background: #f77404;
 color: white;
}  
.pagination a{
 text-decoration: none;
 padding: 10px 15px;
 box-shadow: 0px 0px 15px #0000001c;
 background: white;
 margin: 3px;
 color: #1f1e1e;
}
</style>

  
</head>
<body>
    
<?php

require_once('pagination-script1.php');
 $totalRecordsPerPage=5;
 $tableName='blogs';
 $paginationData=pagination_records($totalRecordsPerPage,$tableName);
 $sn=pagination_records_counter($totalRecordsPerPage);
 $pagination=pagination($totalRecordsPerPage,$tableName);
?>

<!--====pagination content  start====-->
<div class="pagination-content">
    
<?php

foreach ($paginationData as $data) {
?> <h1><?php echo $sn.". ".$data['title'];?></h1> <p><?php echo $data['description'];?></p> <?php

$sn++;}
?>

</div>
<!--====pagination content end====-->
<br><br>
<!--====pagination section start====-->
<div class="pagination">
    
<?php echo $pagination; ?>

</div>
<!--====pagination section end====-->

<br><br><br>
</body>
</html>

 

PHP Pagination Function

I have created custom functions using PHP. It will work to create different types of pagination in PHP.So, you can create other multiple records & multiple paginations of other tables.

For creating more than one pagination, you need not change anything in its main script. just pass the total number of records per page & table name as a parameter to the following function.

pagination_records($totalRecordsPerPage,$tableName);
pagination($totalRecordsPerPage,$tableName);

Suppose that you have to show & create the following records & pagination respectively

If you want to show  –

  • 5 records per page from users table, then you will  have to pass 5 to  the $totalRecordsPerPage & ‘users’ to the $tableName
  • 7 records per page from the students table, then then you will  have to pass 7 to  the $totalRecordsPerPage & ‘student’ to the $tableName
  • 10 records per page from the employees table, then you will  have to pass 10 to  the $totalRecordsPerPage & ’employees’ to the $tableName

My Suggestion

You have learned the best pagination in PHP with the above script. Now, you are able to create another pagination like Blog Pagination, Table pagination & Product Pagination so on.

I hope you like this tutorial. Even it is useful for you. So, Don’t forget to share with friends those who want to learn it.

If you have any queries, you can ask me through the below comment box. I will also share more coding tutorials. So you should continue to visit my blog for becoming an expert in the coding field.