Post
Ask Question

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', '[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') { ?>&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


Similar Articles