In this tutorial we are going to build AJAX Pagination with PHP using jQuery Pagination plugin. With the help of pagination concept we will load list of records/data from MySQL database. Here I am using AJAX to fetch records for each page from database with jQuery.



Nowadays most of the websites have integrated this kind of pagination technique. we have integrated the jQuery pagination plugins, which help to generate pagination buttons as per our need. This plugin help you to create simple, effective, and useful pagination for your site. All you need to do is specify an element (preferably a ul or div) and the plugin takes care of the rest. This pagination requires Jquery library, the plugin itself, and optionally the Bootstrap CSS (as it helps to create formatted button with help of Bootstrap CSS).

In this example, we have two options based on which pagination format will be changed. One is displaying only Prev and Next links to navigate back and forth to the previous and next pages. The other format is displaying all page links and also with the links to navigate to the first, last, previous and next pages.


In this pagination we are limiting the data selection with the help of MySQL Query, Lets learn how to Limit Data Selections From MySQL.

Lets get started to Make Pagination using Ajax with Jquery, PHP and MySQL.

First Create "Post" Database Table

CREATE TABLE `post` (
`POSTID` int(3) NOT NULL,
`POSTTITLE` varchar(100) NOT NULL,
`POSTDETAILS` varchar(10000) NOT NULL,
`POSTLINK` varchar(100) NOT NULL
)

Once You have created above table, Then put the records in this table.

config.php
Consists of database configuration details to establish database connection.

php
/* DATABASE CONFIGURATION */
define('DB_SERVER', 'localhost');
define('DB_DATABASE', 'skptricksdemo');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');

?>

controller.php
Controller work is to control the flow of execution. Here when user click on pagination buttons, then it will process the request and return response to be displayed.

php
include("paginationDAOClass.php");

if(isset($_POST["pageNo"])){

 $group_number = filter_var($_POST["pageNo"], FILTER_SANITIZE_NUMBER_INT, FILTER_FLAG_STRIP_HIGH);
 $paginationClass = new paginationClass();
 echo $paginationClass->displayData($group_number);
}

?>

index.php
This page consists of Jquery, AJAX, PHP and HTML code, which helps to fetch records form MySQL database, when click on pagination button.
Complete work flow :

1. Jquery script helps to identify which all page to load.
2. When user click on Pagination buttons, it will send AJAX Request to controller.php page.
3. controller.php page helps to load records from the MySQL database based on user request and display the results in index.php page

php include("controller.php"); ?>
 lang="en">
 
 rel="stylesheet prefetch" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.0.0-alpha.6/css/bootstrap.min.css" />
src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.min.js">
src="https://cdnjs.cloudflare.com/ajax/libs/twbs-pagination/1.4.1/jquery.twbsPagination.min.js">
type="text/css" >
.wrapper{
   margin: 10px auto;
   text-align: center;
}

#pagination-demo{
   display: inline-block;
   margin-bottom: 1.75em;
}
#pagination-demo li{
   display: inline-block;
}

#post-data{
   width: 500px; border: 2px dotted #d6d7da; 
   padding: 0px 15px 15px 15px; 
   border-radius: 5px;font-family: arial; 
   line-height: 16px;color: #333333; font-size: 14px; 
   background: #ffffff;rgba(200,200,200,0.7) 0 4px 10px -1px;
   margin: 10px auto;
}





 id="load-data"> 

 id="loading"  style="position:relative;left:680px;">  src="loading.gif"  /> 

 class="wrapper">        
 id="pagination-demo" > 



   $(document).ready(function() { 

    $('#pagination-demo').twbsPagination({
     totalPages: php $a = new paginationClass(); echo $a->getTotalNumberOfRecords();  ?>,
     visiblePages: 4,
     next: 'Next',
     prev: 'Prev',
     onPageClick: function (event, page) {           
      loadData(page-1);
}
});

function loadData(pageno){

//ajax request 
     dataString="pageNo="+ pageno ;

     $.ajax({
      type: "POST",
      url: "controller.php" ,
      data: dataString,
      cache: false,
      beforeSend: function(){   
       $( "#loading" ).show();      
},
      success: function(html){           
       $( "#load-data" ).html(  html );
       $( "#loading" ).hide();
}
}); 

}
    loadData(0) ;

}); 

  

paginationDAOClass.php
This DAO class helps to establish the database connection and populate/fetch the records from MySQL database. Controller helps to fetch record from DAO class.

php
 include("config.php");
Class paginationClass{

private $SetRecordLimit = 2 ; 

// connect to mysql database... 
public function DBConnect(){

   $dbhost = DB_SERVER; // set the hostname
   $dbname = DB_DATABASE ; // set the database name
   $dbuser = DB_USERNAME ; // set the mysql username
   $dbpass = DB_PASSWORD;  // set the mysql password

try {
    $dbConnection = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass); 
    $dbConnection->exec("set names utf8");
    $dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $dbConnection;

}
catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}   
}  

// Display Pagination data page wise...
public function displayData( $pageNumber ){   

   $SetOffSetLimit = $pageNumber * $this->SetRecordLimit ;   

try {
    $dbConnection = $this->DBConnect();

    $stmt = $dbConnection->prepare('SELECT * FROM `POST` LIMIT :SetRecordLimit OFFSET :SetOffSetLimit');
    $stmt->bindParam(':SetRecordLimit', $this->SetRecordLimit , PDO::PARAM_INT);
    $stmt->bindParam(':SetOffSetLimit', $SetOffSetLimit , PDO::PARAM_INT);
    $stmt->execute();

    $Count = $stmt->rowCount();     

if ($Count  > 0){
while($data=$stmt->fetch(PDO::FETCH_ASSOC)) {

      echo '
'.$data['POSTTITLE'].'
 '.$data['POSTDETAILS'].'
';

}
}    
}
catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
} 
}  
// Get the Total Number Records...
public function getTotalNumberOfRecords(){

try {
    $dbConnection = $this->DBConnect();

    $stmt = $dbConnection->prepare('SELECT * FROM `POST`');

    $stmt->execute();

    $Count = $stmt->rowCount();     
if ($Count  > 0){
     echo $total_groups = ceil($Count/$this->SetRecordLimit);     
} else{
     echo "0" ;
}
}   
catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}   
} 

} 

?>

Download Link :
https://github.com/skptricks/php-Tutorials/tree/master/Create%20Pagination%20in%20PHP-%20MYSQL-%20JQUERY%20and%20AJAX

This all about Ajax pagination with php and mysql. In case on any queries, please do comment in comment box below.