31
05
Advance Pagination With PHP

In this tutorial we will learn how to create a advance pagination with php ajax. In this I have used bootstrap3 and PHP5. Pagination using ajax is normally used by many developers, to show the data without page refresh. Normally pagination is used while we handling large set of data. Included search option along with pagination to search particular record in the database.

advance pagination with php

Checkout other tutorials of pagination.

List of files that particpate in this pagination technique are:

  • config.php - Use to create connection with mysql
  • index.php - Use to show listing of records using php
  • getData.php - Use to fetch records from database and send to listing page.
  • script.js - Used to communicates to and from a server/database without the need for a postback or a complete page refresh.
    • Step 1: Created connection file db.php and paste below code to create database connection with mysql using PHP.

      $con = mysqli_connect("localhost","root","","hackandphp");
      
      // Check connection
      if (mysqli_connect_errno())
        {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }
      

      Step 2: Created HTML table list and pagination link.

      Sl.No Countries ISO Code ISD Code

      Step 3: Call simple Pagination methods

      function showList(navAction){
      $("#nav_info").html('   Loading...');
      var searchKeyword=$("#LISTNAME_search_txt").val(),currentPage = $("#nav_currentPage").val(),rowsPerPage = $("#nav_rowsPerPage").val();
      $.post("getData.php",{keyword:searchKeyword,currentPage:currentPage,rowsPerPage:rowsPerPage,navAction:navAction,tblCol:3} ,function(data){
      //parse json string to javascript json object
      var data = JSON.parse(data);
      //push data to table
      $("#LISTNAME_tbl tbody").html(data.list);
      //select or focus the target page
      $("#nav_currentPage").val(data.targetPage);
      //check if action is refresh, then reload the GOTO list
      if(navAction=='refresh' || navAction==''){
      //empty the list
      $('#nav_currentPage').empty();
      //append option item with value to select list
      $.each(data.gotoSelectNum, function(key, value) {   
      $('#nav_currentPage')
      .append($("")
      .attr("value",value)
      .text(value)); 
      });
      }
      //show list page and record info
      if(data.totalPages==0){
      $("#nav_info").html('   List Empty');
      }else{
      $("#nav_info").html('   Page '+data.targetPage+' of '+data.totalPages);
      }
      //disable or enable pagination button
      $.each(data.nav_btn_disable, function (key, jdata) {if(jdata==1){$("#"+key).removeClass('disabled');}else{$("#"+key).addClass('disabled');}})
      });						
      }
      

      Step 4: paginatioon.php file use to fetch records from database and bind with tr and return html to pagination ajax method.

      include('config.php');
      $keyword = strip_tags(post($_POST['keyword']));
      $currentPage = intval(post($_POST['currentPage']));
      $rowsPerPage = post($_POST['rowsPerPage']);
      $navAction = post($_POST['navAction']);
      $tblCol = post($_POST['tblCol']);	
      //Applying some condition to SQL
      $sql_condition = 'WHERE';
      if($keyword<>''){$sql_condition.= ($sql_condition=='WHERE'?'':' AND')." (productName LIKE '%$keyword%')";}
      if($sql_condition=='WHERE'){$sql_condition = '';}	
      //Work with total page
      $navRow_qry = exec_query_utf8("SELECT * FROM countries $sql_condition ORDER BY countries_name Asc");
      $totalRow = mysqli_num_rows($navRow_qry);
      
      $totalPages = $totalRow/$rowsPerPage;
      if($totalRow%$rowsPerPage>0){$totalPages = intval($totalPages) + 1;}	
      //Get the target page number	
      $targetPage = 1;$nav_btn_disable = array();
      if($navAction=='first'){
      $targetPage = 1;
      }elseif($navAction=='prev'){
      $targetPage = $currentPage-1;
      }elseif($navAction=='next'){
      $targetPage = $currentPage+1;
      }elseif($navAction=='last'){
      $targetPage = $totalPages;
      }elseif($navAction=='goto'){
      $targetPage = $currentPage;
      }	
      //Get goto select list
      $gotoSelectNum = array();
      for($i=1;$i<=$totalPages;$i++){
      $gotoSelectNum[] = $i;
      }	
      //Check button to be disable or enable
      if($totalPages==1 or $totalPages==0){
      $nav_btn_disable = array('nav_first'=>0,'nav_prev'=>0,'nav_next'=>0,'nav_last'=>0);
      }elseif($targetPage==1){
      $nav_btn_disable = array('nav_first'=>0,'nav_prev'=>0,'nav_next'=>1,'nav_last'=>1);
      }elseif($targetPage==$totalPages){
      $nav_btn_disable = array('nav_first'=>1,'nav_prev'=>1,'nav_next'=>0,'nav_last'=>0);
      }else{
      $nav_btn_disable = array('nav_first'=>1,'nav_prev'=>1,'nav_next'=>1,'nav_last'=>1);
      }	
      //Applying data to be shown according to the criteria [targetPage,rowsPerPage]
      $startIndex = ($targetPage-1)*$rowsPerPage;
      $dataListString = '';$i=$startIndex+1;	
      //Querying data from data
      $data_qry = exec_query_utf8("SELECT * FROM countries $sql_condition ORDER BY countries_name ASC LIMIT ".$startIndex.",$rowsPerPage");
      while($data_row = mysqli_fetch_assoc($data_qry)){		
      $dataListString .= '
      '.$i.'                                    
      '.$data_row['countries_name'].'
      '.$data_row['countries_iso_code'].'
      '.$data_row['countries_isd_code'].'
      ';
      $i++;
      }
      //check if no data in database, then display 'No Data' message
      if($dataListString == ''){$dataListString = ' No Data Found';}
      //store all variable to an array
      $data = array('list'=>$dataListString,'targetPage'=>$targetPage,'totalPages'=>$totalPages,'gotoSelectNum'=>$gotoSelectNum,'nav_btn_disable'=>$nav_btn_disable);
      //convert array to json object, and return it back to ajax
      echo json_encode($data);
      
      You can download source code and Demo from below link.

      By posted on - 31st May 2016