In this article we will learn how to do paging in PHP using Ajax. There is a dropdown box where we can
select how many records we want to display per page. Here
sorting is also available.
Table creation
-- phpMyAdmin SQL Dump
-- version 2.11.4
-- http://www.phpmyadmin.net
-- Host: localhost
-- Generation Time: Nov 22,
2008 at 03:06 AM
-- Server version: 5.0.51
-- PHP Version: 5.2.5
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- Database: `home_work`
-- Table structure for table
`user_info`
CREATE TABLE `user_info` (
`id_user_info` int(11) NOT
NULL auto_increment,
`fullname` varchar(255) NOT
NULL default '',
`email` varchar(255) NOT
NULL default '',
`contact_no` varchar(255)
NOT NULL default '',
PRIMARY KEY (`id_user_info`)
) TYPE=MyISAM AUTO_INCREMENT=24
;
# Dumping data for table `user_info`
INSERT INTO `user_info`
VALUES (1, 'John Smith', '[email protected]', '98675410181');
INSERT INTO `user_info`
VALUES (2, 'Micheal Doughlas', '[email protected]', '98675410131');
INSERT INTO `user_info`
VALUES (3, 'Mary Anderson', '[email protected]', '91675433181');
INSERT INTO `user_info`
VALUES (4, 'James McCarthy', '[email protected]', '98675550131');
INSERT INTO `user_info`
VALUES (5, 'Rosalind Holmes', '[email protected]', '94675433181');
INSERT INTO `user_info`
VALUES (6, 'Pete McCuin', '[email protected]', '98475550131');
INSERT INTO `user_info`
VALUES (7, 'Nicolas Finh', '[email protected]', '98675410181');
INSERT INTO `user_info`
VALUES (8, 'Tommy Lee', '[email protected]', '98675710131');
INSERT INTO `user_info`
VALUES (9, 'Sujoku Jim', '[email protected]', '91675433181');
INSERT INTO `user_info`
VALUES (10, 'James Hull', '[email protected]', '98655550131');
INSERT INTO `user_info`
VALUES (11, 'Sherly Peters', '[email protected]', '94675433181');
INSERT INTO `user_info`
VALUES (12, 'Abraham Holder', '[email protected]', '98400550131');
INSERT INTO `user_info`
VALUES (13, 'Pete Sampras', '[email protected]', '98600010132');
INSERT INTO `user_info`
VALUES (14, 'Kirk Doughlas', '[email protected]', '98675410144');
INSERT INTO `user_info`
VALUES (15, 'Dorothy James', '[email protected]', '91675433165');
INSERT INTO `user_info`
VALUES (16, 'Sebastian Urchin', '[email protected]', '98675550197');
INSERT INTO `user_info`
VALUES (17, 'Tabitha Gregory Golmes', '[email protected]', '94675433133');
INSERT INTO `user_info`
VALUES (18, 'Albert Einstien', '[email protected]', '98475550144');
INSERT INTO `user_info`
VALUES (19, 'Yasser Arafat', '[email protected]', '98675410155');
INSERT INTO `user_info`
VALUES (20, 'Bruce Lee', '[email protected]', '98675710166');
INSERT INTO `user_info`
VALUES (21, 'Johny Depp', '[email protected]', '91675433171');
INSERT INTO `user_info`
VALUES (22, 'Celina Jaitly', '[email protected]', '98655550199');
INSERT INTO `user_info`
VALUES (23,
'Leander Peas', '[email protected]', '94675433100');
config.php
<?php
$DBHOST =
"localhost";
$DBNAME =
"home_work";
$DBUSER =
"root";
$sLink =
mysql_connect($DBHOST,$DBUSER,'')
or
die('Connection
with MySql Server failed');
mysql_select_db($DBNAME, $sLink) or
die('MySql
DB was not found');
?>
Pagination.php
<?php
include('./config.php');
?>
<html>
<head>
<title>User
Information</title>
<script
type="text/javascript"
src="./ajax_sort.js"></script>
<style
type="text/css">
.blackText{
font-weight:normal;
font-size:11.5px;
font-family:
verdana, arial, helvetica, sans-serif;
color:#000000;
font-style:normal;
}
.blackTextDDM{
font-weight:normal;
font-size:11.5px;
font-family:
verdana, arial, helvetica, sans-serif;
color:#000000;
font-style:normal;
padding:
2px;
border:
1px solid #000000;
}
.blueText{
font-weight:normal;
font-size:11px;
font-style:normal;
font-family:
verdana, arial, helvetica, sans-serif;
color:#84C0FC;
}
.errText{
font-weight:italic;
font-size:10.5px;
font-family:
verdana, arial, helvetica, sans-serif;
color:red;
}
</style>
</head>
<body>
<?php
include('./records.php');?>
</body>
</html>
records.php
<?php
$sPageNo =
1;
$sRecordsPerPage
= 5;
$sOrderBy =
"id_user_info
ASC";
if
($_POST['page_no']
|| $_POST['orderby']
|| $_POST['records_per_page'])
{
include('./config.php');
if($_POST['page_no'])
$sPageNo = $_POST['page_no'];
if($_POST['orderby'])
$sOrderBy = $_POST['orderby'];
if($_POST['records_per_page'])
$sRecordsPerPage = $_POST['records_per_page'];
}
$sSelQry =
"SELECT count(*) as no_records FROM user_info";
$aCountRec =
mysql_fetch_array(mysql_query($sSelQry));
$iTotalRecords
= $aCountRec['no_records'];
$sNoOfPages
= ceil($iTotalRecords/$sRecordsPerPage);
$sStartRange
= ($sPageNo * $sRecordsPerPage) - $sRecordsPerPage;
$sEndRange
= $sRecordsPerPage;
$sSelQry =
"SELECT * FROM user_info ORDER BY
$sOrderBy
LIMIT
$sStartRange,$sEndRange";
$aUserInfo =
mysql_query($sSelQry);
if(mysql_num_rows($aUserInfo)
== 0 && $_POST['records_per_page'])
{
$sPageNo
= $sNoOfPages;
$sStartRange
= ($sPageNo * $sRecordsPerPage) - $sRecordsPerPage;
$sEndRange
= $sRecordsPerPage;
$sSelQry
=
"SELECT * FROM user_info ORDER BY
$sOrderBy
LIMIT
$sStartRange,$sEndRange";
$aUserInfo
= mysql_query($sSelQry);
}
//===============================
if
($sOrderBy
==
'fullname ASC')
$sOrderByFN
= 'fullname
DESC';
else
$sOrderByFN
= 'fullname
ASC';
//===============================
if
($sOrderBy
==
'email ASC')
$sOrderByEM
=
'email DESC';
else
$sOrderByEM
=
'email ASC';
//===============================
if
($sOrderBy
==
'contact_no ASC')
$sOrderByCN
= 'contact_no
DESC';
else
$sOrderByCN
= 'contact_no
ASC';
//===============================
?>
<div
id="sorting_rec">
<p
class="blackText"
align="center">
No of
Records Per Page:
<select
class="blackTextDDM"
id="no_of_recs"
name="no_of_recs"
onchange="ajax_sort('<?php
echo($sOrderBy)?>','<?php
echo($sPageNo)?>',this.value)">
<option
value="5"
<?php
if($sRecordsPerPage
== 5) echo
'selected';
?>>5</option>
<option
value="10"
<?php
if($sRecordsPerPage
== 10) echo
'selected';
?>>10</option>
<option
value="20"
<?php
if($sRecordsPerPage
== 20) echo
'selected';
?>>20</option>
</select>
</p>
<p
align="center"><span
class="blueText">
<?php
for($j=1;
$j<=$sNoOfPages; $j++) {
?>
<a
style="cursor:pointer;"
onclick="ajax_sort('<?php
echo($sOrderBy)?>','<?php
echo($j)?>','<?php
echo($sRecordsPerPage)?>')"><?php
if($sPageNo
== $j){ echo
"<b style='color:#295F8B;'>".$j."</b>
"; } else
{ echo($j."
"); } ?></a>
<?php
} ?>
</span></p>
<table
cellspacing="1"
style="border:
1px solid #000000;
width:550px;"
align="center">
<tr
style="background-color:#70988D">
<td
class="blackText"
style="width:60px"
align="center"><b>Sl.
No.</b></td>
<td
class="blackText"
style="width:150px"
align="center"><b><a
style="cursor:pointer;"
onclick="ajax_sort('<?php
echo($sOrderByFN)?>','<?php
echo($sPageNo)?>','<?php
echo($sRecordsPerPage)?>')">Fullname</a></b><?php
if($sOrderBy
== 'fullname ASC')
{ ?> <img
src="./s_asc.png"><?php
} elseif($sOrderBy
== 'fullname DESC')
{ ?> <img
src="./s_desc.png"><?php
} ?></td>
<td
class="blackText"
style="width:220px"
align="center"><b><a
style="cursor:pointer;"
onclick="ajax_sort('<?php
echo($sOrderByEM)?>','<?php
echo($sPageNo)?>','<?php
echo($sRecordsPerPage)?>')">Email</a></b><?php
if($sOrderBy
== 'email ASC')
{ ?> <img
src="./s_asc.png"><?php
} elseif($sOrderBy
== 'email DESC')
{ ?> <img
src="./s_desc.png"><?php
} ?></td>
<td
class="blackText"
style="width:120px"
align="center"><b><a
style="cursor:pointer;"
onclick="ajax_sort('<?php
echo($sOrderByCN)?>','<?php
echo($sPageNo)?>','<?php
echo($sRecordsPerPage)?>')">Contact
No</a></b><?php
if($sOrderBy
== 'contact_no ASC')
{ ?> <img
src="./s_asc.png"><?php
} elseif($sOrderBy
== 'contact_no DESC')
{ ?> <img
src="./s_desc.png"><?php
} ?></td>
</tr>
<?php
$i = ($sRecordsPerPage
* $sPageNo) - ($sRecordsPerPage - 1);
while($aUserInfoRS
= mysql_fetch_array($aUserInfo)) {
$sUserName =
$aUserInfoRS['fullname'];
$sEmail =
$aUserInfoRS['email'];
$sContact =
$aUserInfoRS['contact_no'];
?>
<tr
style="background-color:#CCDAD6;">
<td
class="blackText"
align="center"><?php
echo($i)?></td>
<td
class="blackText"><?php
echo($sUserName)?></td>
<td
class="blackText"><?php
echo($sEmail)?></td>
<td
class="blackText"
align="center"><?php
echo($sContact)?></td>
</tr>
<?php
$i++;
}
?>
</table>
</div>
ajax_sort.js
function
ajax_sort(sort_order, page_no, records_per_page) {
http_request =
false;
if (window.XMLHttpRequest) {
// Mozilla, Safari,...
http_request =
new XMLHttpRequest();
if (http_request.overrideMimeType) {
// set type accordingly to
anticipated content type
//http_request.overrideMimeType('text/xml');
http_request.overrideMimeType('text/html');
}
}
else if (window.ActiveXObject)
{ // IE
try
{Z
http_request =
new ActiveXObject("Msxml2.XMLHTTP");
}
catch (e) {
try {
http_request = new ActiveXObject("Microsoft.XMLHTTP");
}
catch (e) { }
}
}
if (!http_request) {
alert('Cannot
create XMLHTTP instance');
return false;
}
var url = 'records.php';
var parameters = 'orderby='
+ sort_order + '&page_no=' + page_no +
'&records_per_page=' + records_per_page;
http_request.onreadystatechange = ajax_sort_callback;
http_request.open('POST',
url, true);
http_request.setRequestHeader("Content-type",
"application/x-www-form-urlencoded");
http_request.setRequestHeader("Content-length",
parameters.length);
http_request.setRequestHeader("Connection",
"close");
http_request.send(parameters);
}
function ajax_sort_callback() {
if (http_request.readyState == 4) {
if (http_request.status == 200) {
result =
http_request.responseText;
//alert(result);
//return false;
document.getElementById('sorting_rec').innerHTML
= result;
}
else {
alert('There
was a problem with the request.');
}
}
}
Running the application
Run the
WampServer then write the below line in the Url.
OUTPUT
![pic3.gif]()
![pic4.gif]()