Advance Pagination With PHP

31
05Advance 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.

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);

By Thirumani Raj posted on - 31st May 2016