Paging in PHP


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', 'john.smith@ymail.com', '98675410181');
INSERT INTO `user_info` VALUES (2, 'Micheal Doughlas', 'michael.doughlas@hotmail.com', '98675410131');
INSERT INTO `user_info` VALUES (3, 'Mary Anderson', 'mary.anderson@gmail.com', '91675433181');
INSERT INTO `user_info` VALUES (4, 'James McCarthy', 'mac.james@hotmail.com', '98675550131');
INSERT INTO `user_info` VALUES (5, 'Rosalind Holmes', 'rosalindh@ymail.com', '94675433181');
INSERT INTO `user_info` VALUES (6, 'Pete McCuin', 'pete.cuin@yahoo.co.uk', '98475550131');
INSERT INTO `user_info` VALUES (7, 'Nicolas Finh', 'nicolas.finh@ymail.com', '98675410181');
INSERT INTO `user_info` VALUES (8, 'Tommy Lee', 'tommy.lee@hotmail.com', '98675710131');
INSERT INTO `user_info` VALUES (9, 'Sujoku Jim', 'sujoku.jim@gmail.com', '91675433181');
INSERT INTO `user_info` VALUES (10, 'James Hull', 'james.hull@hotmail.com', '98655550131');
INSERT INTO `user_info` VALUES (11, 'Sherly Peters', 'sherlyp@ymail.com', '94675433181');
INSERT INTO `user_info` VALUES (12, 'Abraham Holder', 'abraham.holder@yahoo.co.uk', '98400550131');
INSERT INTO `user_info` VALUES (13, 'Pete Sampras', 'pete.sampras@ymail.com', '98600010132');
INSERT INTO `user_info` VALUES (14, 'Kirk Doughlas', 'kirk.doughlas@hotmail.com', '98675410144');
INSERT INTO `user_info` VALUES (15, 'Dorothy James', 'dorothy.james@gmail.com', '91675433165');
INSERT INTO `user_info` VALUES (16, 'Sebastian Urchin', 'sebastian.urchin@hotmail.com', '98675550197');
INSERT INTO `user_info` VALUES (17, 'Tabitha Gregory Golmes', 'tabithagg@ymail.com', '94675433133');
INSERT INTO `user_info` VALUES (18, 'Albert Einstien', 'albert.einstien@yahoo.co.uk', '98475550144');
INSERT INTO `user_info` VALUES (19, 'Yasser Arafat', 'yasser.arafat@ymail.com', '98675410155');
INSERT INTO `user_info` VALUES (20, 'Bruce Lee', 'bruce.lee@hotmail.com', '98675710166');
INSERT INTO `user_info` VALUES (21, 'Johny Depp', 'johny.depp@gmail.com', '91675433171');
INSERT INTO `user_info` VALUES (22, 'Celina Jaitly', 'celina.jaitly@hotmail.com', '98655550199');
INSERT INTO `user_info` VALUES (23, 'Leander Peas', 'leanderpeas@ymail.com', '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') { ?>&nbsp;<img src="./s_asc.png"><?php } elseif($sOrderBy == 'fullname DESC') { ?>&nbsp;<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') { ?>&nbsp;<img src="./s_asc.png"><?php } elseif($sOrderBy == 'email DESC') { ?>&nbsp;<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') { ?>&nbsp;<img src="./s_asc.png"><?php } elseif($sOrderBy == 'contact_no DESC') { ?>&nbsp;<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.

http://localhost/Pagination in Php/

OUTPUT

pic3.gif

pic4.gif