Sunday, 21 April 2013

How to create 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('<?phpecho($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)?>','<?phpecho($sRecordsPerPage)?>')">Fullname</a></b><?php if($sOrderBy == 'fullname ASC') { ?>&nbsp;<img src="./s_asc.png"><?php } elseif($sOrderBy == 'fullname DESC') { ?>&nbsp;<imgsrc="./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)?>','<?phpecho($sRecordsPerPage)?>')">Email</a></b><?php if($sOrderBy == 'email ASC') { ?>&nbsp;<imgsrc="./s_asc.png"><?php } elseif($sOrderBy == 'email DESC') { ?>&nbsp;<imgsrc="./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)?>','<?phpecho($sRecordsPerPage)?>')">Contact No</a></b><?php if($sOrderBy == 'contact_no ASC') { ?>&nbsp;<img src="./s_asc.png"><?php } elseif($sOrderBy == 'contact_no DESC') { ?>&nbsp;<imgsrc="./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
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)