Paging Script using AJAX PHP & MySQL breaking records into pages

Ajax based solutions are popular as it is not required to reload the page. Here is the code to learn Ajax by applying to our paging script. Read this if you are new to Ajax. You can also read our basic paging script.

We are using our student table which have 35 records. Number of records per page is fixed at 10 so we will have Forward and Backward movement buttons to navigate to previous and next page of records. Each page will display 10 records and last page will show balance five records.

We have used button style property to mange the display. Once we are in last page or record display then we will not display the Forward navigational button. Same way Previous button can be hidden while displaying the fist set of 10 records.

In this Ajax solution we have used one html file which have all the Ajax ( JavaScript ) Code and to handle the backend database management one PHP file is used.
Demo of Ajax Paging script

Installation

1.Open config.php file and enter your database login details
2.Open mysql-dump.txt file and use the sql to create your student table
3.Keep the json2.js file ( after downloading )

How Paging script works

Here main difference is collecting the records from backend script without reloading the page. To receive the data and to post data we need to use Ajax. Let us start with how to post data to backend script.

php_paging-ajax-demo.php
We use JavaScript function ajaxFunction(val) to trigger the Ajax and post data. This function receives the information about the button clicked by the user. It can be forward or backward button.

Two parameters are posted to backed script. One is direction ( forward or backward ) and other one is value of end record. The value of end record is 10 for the first page ( if we are displaying 10 records per page ) or the value of end record is 20 if we are in second page.

Taking these two values we prepare the query string and post the data to our backend script php_paging-ajaxck.php using GET method of form posting.

var url="php_paging-ajaxck.php";
var myendrecord=myForm.end_record.value;
url=url+"?endrecord="+myendrecord;
url=url+"&direction="+val;

url=url+"&sid="+Math.random();
httpxml.onreadystatechange=stateChanged;
httpxml.open("GET",url,true);
httpxml.send(null);

php_paging-ajaxck.php
This is our backend script which receives information from our demo page and process to get the required number of pages from student table. It receives two parameters. One is direction ( which button is pressed by user ) and other is value of end record.

Before using end of record value we will sanitize the data to prevent any injection attack.

We will set 10 records per page here but you can change to any value you want.

$endrecord=$_GET['endrecord'];// 
if(strlen($endrecord) > 0 AND (!is_numeric($endrecord))){
echo "Data Error";
exit;
}

For paging we will find out total number of records present in our table matching to our requirement.

$total_records = $dbo->query("select count(id) from student")->fetchColumn();

We can set number of records to be displayed in a page.

$limit=10; // Number of records per page, you can change this value

Now based on direction button pressed we will set the value of start record. We will be using limit query to get a set of records from the table so start record is required for the query we will use to get the set of records from student table.

Setting the $end to yes

For example if we are in fourth page of student table ( total number of records in table is 35 ) then our end record number will be 40 so we should not show the Next button to users. As long as our end record value is less than total number of records, we should show the forward button. For this we will set the $end variable to yes.

if(($endrecord) < $total_records ){$end="yes";}  // managing forward button
else{$end="no";}

Similarly to manage the backward button we should check that $endrecord is more than value set for $limit.

if(($endrecord) > $limit ){$startrecord="yes";}    // managing reverse button
else{$startrecord="no";}

With this we will return data to front end page by using Json.

$main = array('data'=>$result,'value'=>array("endrecord"=>"$endrecord",
"limit"=>"$limit","end"=>"$end","startrecord"=>"$startrecord"));
echo json_encode($main);

Receiving the data

We will first get the Json data in our home page.

var myObject = JSON.parse(httpxml.responseText);

Here myObject is an array with records and data. We know that it has one more array inside named data. We will loop through this array to display the records.

for(i=0;i<myObject.data.length;i++)
{ 
str = str + "<tr><td>" + myObject.data[i].id + " </td><td>" + myObject.data[i].name + 

 " </td><td>" + myObject.data[i].myclass + " </td><td>" + myObject.data[i].mark + "</td></tr>"
}

Now let us store the value of endrecord which is required for further posting back to backend script.

var endrecord=myObject.value.endrecord

We have two navigational buttons, Previous button and next button to move to previous and next set of records. At first page we need to hide Previous button and in last page we need to hide Next button. We will manage the display of buttons by using its style property.

myForm.end_record.value=endrecord;
if(myObject.value.end =="yes"){ document.getElementById("fwd").style.display='inline';
}else{document.getElementById("fwd").style.display='none';}


if(myObject.value.startrecord =="yes"){ document.getElementById("back").style.display='inline';
}else{document.getElementById("back").style.display='none';}

Ajax based PHP script with record DELETE option
Ajax Json XML

Subscribe to our YouTube Channel here

arifxxx	13-06-2014
this is one of the best tutorial what i've know

subbu	26-02-2017
how to implement or add one more button that retrieve all records at once? can you provide the code for that? thanks.

smo1234	28-02-2017
Your query reads like this $query=" SELECT * FROM student limit $eu, $limit "; If you remove limit part and make it like this. $query=" SELECT * FROM student "; You can display all the records. With the button you can pass a variable like this <a href=page_name.php?display_status=dispaly_all>Show all</a> Collect display_status like this at the top of the page. $display_all=$_GET['display_all']; if($dispaly_all=='display_all'){ $q='SELECT * FROM student'; }else{ $q='SELECT * FROM student limit $eu,$limit'; }

Php Developer	01-06-2018
Nice work! This is massively helpful to me. It just made my work easier. It saved lot of time. Thanks.

Ajax Paging script using PHP MySQL

Installation

How Paging script works

Setting the $end to yes

Receiving the data

Subscribe