Menu
HI,
trying to give page numbers for the list of records from the database…
there are some 1000 records in the database so i want to display 15 records per page by giving page numbers and next button options. can anybody help me?…
[code=php]
<?php
session_start();
//----------------------------------------------------------------------------------------------------------------------
//connect to the database
$DBUserName = "root"; //database user account name
$DBPassword = ""; //database user account password
$DBName = "test"; //name of database
@$conn = mysql_connect("localhost",$DBUserName,$DBPassword) or die('<br />1-Cannot connect to the database at the moment.<br /><br />Please try again later.<br />'); //connect to mysql
@$isDbSelected = mysql_select_db($DBName, $conn) or die('<br />1-Cannot connect to the database at the moment.<br /><br />Please try again later.<br />'); //connect to the db
//----------------------------------------------------------------------------------------------------------------------
$linesPerPage = 4; //number of lines to print per page
//check if a page link was clicked
if(isset($_GET['txtPgNum']))
{
$_SESSION['currPage'] = $_GET['txtPgNum']; //txtPgNum comes from the page links
unset($_GET['txtPgNum']);
}
//check if either 'previous' or 'next' buttons were clicked
else if(isset($_GET['dir']))
{
$direction = $_GET['dir']; // -1 = prev page 1 = next page
unset($_GET['dir']);
$_SESSION['currPage'] = $_SESSION['currPage'] + $direction;
if($_SESSION['currPage'] < 1) $_SESSION['currPage'] = 1;
if($_SESSION['currPage'] > $_SESSION['totPages']) $_SESSION['currPage'] = $_SESSION['totPages'];
}
else //neither a page link or previous or next buttons were clicked. This must then be the first call to this page
{
$_SESSION['currPage'] = 1; //set current page = 1
//select all the records to work out max number of rows and number of pages needed
$query = 'select * from tblperson';
$rs = mysql_query($query,$conn) or die("<p>3-Server is busy.<br />Please try again later.</p>");
$totRows = mysql_num_rows($rs); //total number of rows to display
if($totRows%$linesPerPage == 0)
$totPages = $totRows/$linesPerPage;
else
$totPages = round(($totRows/$linesPerPage)+0.5); //total number of pages required
mysql_free_result($rs);
$_SESSION['totRows'] = $totRows;
$_SESSION['totPages'] = $totPages;
}
//calculate the first record number to retrieve from the DB for this page
$_SESSION['offset'] = ($_SESSION['currPage']*$linesPerPage)-$linesPerPage;
if($_SESSION['offset'] < 0) $_SESSION['offset'] = 0;
if($_SESSION['offset'] > $_SESSION['totRows']) $_SESSION['offset'] = $_SESSION['totRows'];
//------------------------------------------------------------
//Code to retrieve the rows to display on the current page
//------------------------------------------------------------
$query = 'select * from tblperson limit '.$_SESSION['offset'].','.$linesPerPage;
$rs = mysql_query($query,$conn) or die("<p>3-Server is busy.<br />Please try again later.</p>");
?>
<html>
<head>
<title>Pagination</title>
<style type="text/css">
<!--
body {
font-size: 10pt;
font-family: tahoma, arial, sans serif}
#page-links-container {
margin: 0px 0px 0px 0px;
padding: 0px 0px 0px 0px}
#page-links-container ul {
list-style-type: none}
#page-links-container ul li {
display: inline;
color: rgb(0,0,205);
padding: 3px 4px 3px 4px;
margin: 0px 0px 0px 6px}
#page-links-container ul li a {
text-decoration: none;
font-size: 10pt}
#page-links-container ul li a:hover {
text-decoration: underline}
#page-links-container ul li a:visited {
color: rgb(0,0,205);}
-->
</style>
</head>
<body>
<!-- Start of page links container -->
<div id="page-links-container">
<ul id="page-links">
<li id="liPrevPage"><a href="pagination.php?dir=-1" title="Click to view previous page">Previous</a></li>
<?php
for($i=1; $i<=$_SESSION['totPages']; $i=$i+1) {
echo '<li id="liPg'.$i.'"><a href="pagination.php?txtPgNum='.$i.'" title="Go to page '.$i.'">'.$i.'</a></li>';
}
?>
<li id="liNextPage"><a href="pagination.php?dir=1" title="Click to view next page">Next</a></li>
</ul>
</div>
<!-- end of page-links-container div -->
<!-- Code block to display the DB data from row $_SESSION['offset'] to row $_SESSION['offset']+$linesPerPage -->
<table>
<?php
while($row=mysql_fetch_assoc($rs))
{
echo '<tr><td>'.$row['fldPersonID'].'</td><td>'.$row['fldGivenName'].'</td><td>'.$row['fldFamilyName'].'</td></tr>';
}
mysql_free_result($rs);
mysql_close($conn);
?>
</table>
<!-- End of code block to display the DB data from row $_SESSION['offset'] to row $_SESSION['offset']+$linesPerPage -->
<?php
//hide or display the 'previous' and 'next' buttons as required
if($_SESSION['totPages'] == 1)
echo '<script type="text/javascript">document.getElementById("page-links-container").style.display="none";</script>';
if($_SESSION['currPage'] == 1)
echo '<script type="text/javascript">document.getElementById("liPrevPage").style.visibility="hidden";</script>';
else
echo '<script type="text/javascript">document.getElementById("liPrevPage").style.visibility="visible";</script>';
if($_SESSION['currPage'] == $_SESSION['totPages'])
echo '<script type="text/javascript">document.getElementById("liNextPage").style.visibility="hidden";</script>';
else
echo '<script type="text/javascript">document.getElementById("liNextPage").style.visibility="visible";</script>';
//highlight the current page's page link
echo '<script type="text/javascript">document.getElementById("liPg'.$_SESSION['currPage'].'").style.backgroundColor="rgb(200,200,200)";</script>';
echo '<script type="text/javascript">document.getElementById("liPg'.$_SESSION['currPage'].'").style.border="1px solid rgb(0,0,0)";</script>';
?>
</body>
</html>
[/code][CODE]
CREATE TABLE tblperson (
fldPersonID int(11) NOT NULL AUTO_INCREMENT,
fldFamilyName varchar(20) DEFAULT NULL,
fldGivenName varchar(20) DEFAULT NULL,
PRIMARY KEY (fldPersonID)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
/*Data for the table tblperson */
insert into tblperson(fldPersonID,fldFamilyName,fldGivenName) values (1,'Sui','Steven');
insert into tblperson(fldPersonID,fldFamilyName,fldGivenName) values (2,'Student','Mary');
insert into tblperson(fldPersonID,fldFamilyName,fldGivenName) values (3,'Student','Sam');
insert into tblperson(fldPersonID,fldFamilyName,fldGivenName) values (4,'Malik','Moore');
insert into tblperson(fldPersonID,fldFamilyName,fldGivenName) values (5,'Soo','Malinda');
insert into tblperson(fldPersonID,fldFamilyName,fldGivenName) values (6,'flinstone','fred');
insert into tblperson(fldPersonID,fldFamilyName,fldGivenName) values (7,'rubble','barney');
insert into tblperson(fldPersonID,fldFamilyName,fldGivenName) values (8,'flinstone','wilma');
insert into tblperson(fldPersonID,fldFamilyName,fldGivenName) values (9,'flinstone','pebbles');
insert into tblperson(fldPersonID,fldFamilyName,fldGivenName) values (10,'smart','maxwell');
insert into tblperson(fldPersonID,fldFamilyName,fldGivenName) values (11,'Soo','Malinda');
[/CODE]//select all the records to work out max number of rows and number of pages needed
$query = 'select * from tblperson';
$rs = mysql_query($query,$conn) or die("<p>3-Server is busy.<br />Please try again later.</p>");
$totRows = mysql_num_rows($rs); //total number of rows to display
if($totRows%$linesPerPage == 0)
$totPages = $totRows/$linesPerPage;
else
$totPages = round(($totRows/$linesPerPage)+0.5); //total number of pages required
mysql_free_result($rs);0.1.9 — BETA 5.4