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.

• Bootstrap Responsive Date Pagination with PHP Mysql
• Datatable - Searching, Sorting and Pagination on server side using Php , Mysql, Ajax, Json

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.

view plaincopy to clipboardprint?
1. $con = mysqli_connect("localhost","root","","hackandphp");  
2.   
3. // Check connection  
4. if (mysqli_connect_errno())  
5.   {  
6.   echo "Failed to connect to MySQL: " . mysqli_connect_error();  
7.   }  

$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.

view plaincopy to clipboardprint?
1. <table class="table table-striped table-bordered table-hover" id="LISTNAME_tbl">  
2. <thead>  
3. <tr>  
4. <th>Sl.No</th>  
5. <th>Countries</th>  
6. <th>ISO Code</th>  
7. <th>ISD Code</th>  
8. </tr>  
9. </thead>  
10. <tbody id="">                                              
11. </tbody>  
12. </table>  

Sl.No
Countries
ISO Code
ISD Code


                                            

Step 3: Call simple Pagination methods

view plaincopy to clipboardprint?
1. function showList(navAction){  
2. $("#nav_info").html('<span><img src="loader.gif" width="16">   Loading...</span>');  
3. var searchKeyword=$("#LISTNAME_search_txt").val(),currentPage = $("#nav_currentPage").val(),rowsPerPage = $("#nav_rowsPerPage").val();  
4. $.post("getData.php",{keyword:searchKeyword,currentPage:currentPage,rowsPerPage:rowsPerPage,navAction:navAction,tblCol:3} ,function(data){  
5. //parse json string to javascript json object  
6. var data = JSON.parse(data);  
7. //push data to table  
8. $("#LISTNAME_tbl tbody").html(data.list);  
9. //select or focus the target page  
10. $("#nav_currentPage").val(data.targetPage);  
11. //check if action is refresh, then reload the GOTO list  
12. if(navAction=='refresh' || navAction==''){  
13. //empty the list  
14. $('#nav_currentPage').empty();  
15. //append option item with value to select list  
16. $.each(data.gotoSelectNum, function(key, value) {     
17. $('#nav_currentPage')  
18. .append($("<option></option>")  
19. .attr("value",value)  
20. .text(value));   
21. });  
22. }  
23. //show list page and record info  
24. if(data.totalPages==0){  
25. $("#nav_info").html('<i class="fa fa-file-o"></i>   List Empty');  
26. }else{  
27. $("#nav_info").html('<i class="fa fa-file-o"></i>   Page '+data.targetPage+' of '+data.totalPages);  
28. }  
29. //disable or enable pagination button  
30. $.each(data.nav_btn_disable, function (key, jdata) {if(jdata==1){$("#"+key).removeClass('disabled');}else{$("#"+key).addClass('disabled');}})  
31. });                       
32. }  

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.

view plaincopy to clipboardprint?
1. include('config.php');  
2. $keyword = strip_tags(post($_POST['keyword']));  
3. $currentPage = intval(post($_POST['currentPage']));  
4. $rowsPerPage = post($_POST['rowsPerPage']);  
5. $navAction = post($_POST['navAction']);  
6. $tblCol = post($_POST['tblCol']);     
7. //Applying some condition to SQL  
8. $sql_condition = 'WHERE';  
9. if($keyword<>''){$sql_condition.= ($sql_condition=='WHERE'?'':' AND')." (productName LIKE '%$keyword%')";}  
10. if($sql_condition=='WHERE'){$sql_condition = '';}     
11. //Work with total page  
12. $navRow_qry = exec_query_utf8("SELECT * FROM countries $sql_condition ORDER BY countries_name Asc");  
13. $totalRow = mysqli_num_rows($navRow_qry);  
14.   
15. $totalPages = $totalRow/$rowsPerPage;  
16. if($totalRow%$rowsPerPage>0){$totalPages = intval($totalPages) + 1;}   
17. //Get the target page number      
18. $targetPage = 1;$nav_btn_disable = array();  
19. if($navAction=='first'){  
20. $targetPage = 1;  
21. }elseif($navAction=='prev'){  
22. $targetPage = $currentPage-1;  
23. }elseif($navAction=='next'){  
24. $targetPage = $currentPage+1;  
25. }elseif($navAction=='last'){  
26. $targetPage = $totalPages;  
27. }elseif($navAction=='goto'){  
28. $targetPage = $currentPage;  
29. }     
30. //Get goto select list  
31. $gotoSelectNum = array();  
32. for($i=1;$i<=$totalPages;$i++){  
33. $gotoSelectNum[] = $i;  
34. }     
35. //Check button to be disable or enable  
36. if($totalPages==1 or $totalPages==0){  
37. $nav_btn_disable = array('nav_first'=>0,'nav_prev'=>0,'nav_next'=>0,'nav_last'=>0);  
38. }elseif($targetPage==1){  
39. $nav_btn_disable = array('nav_first'=>0,'nav_prev'=>0,'nav_next'=>1,'nav_last'=>1);  
40. }elseif($targetPage==$totalPages){  
41. $nav_btn_disable = array('nav_first'=>1,'nav_prev'=>1,'nav_next'=>0,'nav_last'=>0);  
42. }else{  
43. $nav_btn_disable = array('nav_first'=>1,'nav_prev'=>1,'nav_next'=>1,'nav_last'=>1);  
44. }     
45. //Applying data to be shown according to the criteria [targetPage,rowsPerPage]  
46. $startIndex = ($targetPage-1)*$rowsPerPage;  
47. $dataListString = '';$i=$startIndex+1;    
48. //Querying data from data  
49. $data_qry = exec_query_utf8("SELECT * FROM countries $sql_condition ORDER BY countries_name ASC LIMIT ".$startIndex.",$rowsPerPage");  
50. while($data_row = mysqli_fetch_assoc($data_qry)){         
51. $dataListString .= '  
52. '.$i.'                                      
53. '.$data_row['countries_name'].'  
54. '.$data_row['countries_iso_code'].'  
55. '.$data_row['countries_isd_code'].'  
56. ';  
57. $i++;  
58. }  
59. //check if no data in database, then display 'No Data' message  
60. if($dataListString == ''){$dataListString = '<i class="fa fa-frown-o"></i> No Data Found';}  
61. //store all variable to an array  
62. $data = array('list'=>$dataListString,'targetPage'=>$targetPage,'totalPages'=>$totalPages,'gotoSelectNum'=>$gotoSelectNum,'nav_btn_disable'=>$nav_btn_disable);  
63. //convert array to json object, and return it back to ajax  
64. echo json_encode($data);  

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 Thirumani Raj posted on - 31st May 2016

Live Demo Download Script