Most users when works with large databases for websites with much more records in it and when want to list all results, experiencing problems how to display the data and how to keep small and nice looking web page with fast page load time.
Very elegant way to workaround with this is to display the limited result using SELECT * FROM `table` LIMIT $from, $how_much where:
$from - from which result to start listing
$how_much - how much results to list
And below the table with listed results to display paging navigation like this:
[1] 2 3 4 5
Next when user clicks on any button he can navigate in results by sending different values for $from parameter using GET or POST method. In our case this will be made with AJAX for prevent page loading and faster paging in the results.
In this tutorial I will show you exactly this - how to make Paged Result Query with AJAX using PHP & MySQL.
Now lets start with configuration of main parameters for data base connection and page result listing. This is our config.php file which we will include in several other files later:
Paging using PHP & MySQL with AJAX
|
<?php
// mysql database connection setup
$host='localhost';
$username='root';
$password='cP87-31^s0h';
$db_name='users';
// how many results to display in one page
$results_per_page=5;
// connect and select data base
$connect=mysql_connect($host, $username, $password);
$dbase=mysql_select_db($db_name);
?>
Here is the complete code for MySql query for listing of default first page. Please note that this script uses AJAX function which makes GET request to the server to get only one page from all results.
<html>
<head>
<title>Paged Result Query with AJAX using PHP & MySQL</title>
<script language="JavaScript" type="text/javascript">
function goto_page(page) {
xmlhttp=GetXmlHttpObject();
if (xmlhttp==null) {
alert ("Your browser does not support AJAX!");
return;
}
var url="ajax.php";
url=url+"?page="+page;
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4 || xmlhttp.readyState=="complete") {
document.getElementById('results').innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET",url,true);
xmlhttp.send(null);
}
function GetXmlHttpObject() {
var xmlhttp=null;
try {
// Firefox, Opera 8.0+, Safari
xmlhttp=new XMLHttpRequest();
}
catch (e) {
// Internet Explorer
try {
xmlhttp=new ActiveXObject("Msxml2.XMLHTTP");
}
catch (e) {
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
}
return xmlhttp;
}
</script>
</head>
<body>
<?php
// include config file
include("config.php");
$result=mysql_query("select * from `users`");
$all_result=mysql_num_rows($result);
// calculate total number of pages needed
$pages=ceil($all_result/$results_per_page);
$get_first_result=mysql_query("select * from `users` limit 0, $results_per_page");
echo "<table id=\"results\">";
// list first page results
while(list($id,$username,$full_name,$email,$password)=mysql_fetch_row($get_first_result)) {
echo "<tr>\n
<td>".$id."</td>\n
<td>".$username."</td>\n
<td>".$full_name."</td>\n
<td>".$email."</td>\n
<td>".$password."</td>\n
</tr>\n";
}
// list page navgation ([1] 2 3 4 5)
echo "<tr>\n";
echo "<td colspan=\"5\">\n";
$page=1;
while($pages) {
if ($page==1) {
echo "<a href=\"javascript:goto_page(".$page.");\"><b>[".$page."]</b></a> \n";
} else {
echo "<a href=\"javascript:goto_page(".$page.");\">".$page."</a> \n";
}
$pages--;
$page++;
}
echo "</td>\n";
echo "</tr>\n";
echo "</table>";
?>
</body>
</html>
Here is AJAX server side PHP file which catch GET request for wanted page (sent like url parameter), makes MySql query for this page and returns results only for this page. Like in our example you have to save this into file named ajax.php in the same folder:
<?php
// include config file
include("config.php");
if (is_numeric($_GET['page'])) {
// calculate starting position for listing the results
$offset=$results_per_page*($_GET['page']-1);
$get_current_result=mysql_query("select * from `users` limit $offset, $results_per_page");
// list results for current page
while(list($id,$username,$full_name,$email,$password)=mysql_fetch_row($get_current_result)) {
echo "<tr>\n
<td>".$id."</td>\n
<td>".$username."</td>\n
<td>".$full_name."</td>\n
<td>".$email."</td>\n
<td>".$password."</td>\n
</tr>\n";
}
// list page navgation ([1] 2 3 4 5)
echo "<tr>\n";
echo "<td colspan=\"5\">\n";
$page=1;
while($pages) {
if ($page==$_GET['page']) {
echo "<a href=\"javascript:goto_page(".$page.");\"><b>[".$page."]</b></a> \n";
} else {
echo "<a href=\"javascript:goto_page(".$page.");\">".$page."</a> \n";
}
$pages--;
$page++;
}
echo "</td>\n";
echo "</tr>\n";
}
?>
Now let see how all of this got work: