Create Pagination in PHP, MYSQL, JQUERY and AJAX

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.

custom pagination in php, how to create pagination in php and mysql with ajax

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.

Bootstrap pagination in PHP and 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 :

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

Lets see the complete source code for index.php page.

<?php include("controller.php"); ?>
<html lang="en">
 <head> 
  <link rel="stylesheet prefetch" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.0.0-alpha.6/css/bootstrap.min.css" />
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/twbs-pagination/1.4.1/jquery.twbsPagination.min.js"></script>
  <style 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;
   }
   
  </style>
 </head>
 <body>
  
  <div id="load-data"> </div>
  
  <span id="loading"  style="position:relative;left:680px;"> <img src="loading.gif"  /> </span>
  
  <div class="wrapper">        
   <ul id="pagination-demo" ></ul> 
  </div>
  
  <script>
   $(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) ;
    
   }); 
   
  </script>  
 </body>
</html>

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 '<div id="post-data"><h4>'.$data['POSTTITLE'].'</h4><div> '.$data['POSTDETAILS'].'</div></div>';
      
     }
    }    
   }
   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.

3 comments:

UnknownOctober 24, 2018 at 8:42 PM
how to add title link ?
Mueeid SoomroDecember 22, 2018 at 2:46 AM
First released in May 23, 1995, MySQL is an open source database software. It is part of a large and growing family of open source software including Linux, Apache, and programming languages PHP and Perl.dbdesigner.net

Sunday, January 28, 2018

Create Pagination in PHP, MYSQL, JQUERY and AJAX

Bootstrap pagination in PHP and MySQL

3 comments:

Follow Us

Get Latest Updates

Tags

Popular Posts

Skptricks