Page 1 of 1

paging results

Posted: Fri Sep 26, 2003 6:05 pm
by sulen
I am obtaining multiple records from a table in the database, I need to format it in such a way that I can return 25 results per page with prev and next links. This is the copy of my code, please let me know how I can page my db results

<?php
//connecting to the database
@ $db = mysql_pconnect("localhost", "root", "dbstuff3r");

//connection error
if(!$db)
{
echo "error could not connect to the database. please try again later";
exit;
}

// rows to return

//selecting database
mysql_select_db("axis");
$sql = "select * from login where login='$login' and pwd='$pwd'";
$result = mysql_query($sql);
$count = mysql_num_rows($result);

if(!$result)
{
echo"<p>Cannot execute query</p>";
}

if($count<1)
{
echo "<p>User needs to enter correct Site Number</p>";
}
else
{
for($i=0; $i<$count; $i++)
{
$row = mysql_fetch_array($result);
$alevel=$row[2];
$region=$row[3];
$branch=$row[4];
if ($alevel=='branch')
{
$sql1 = "select * from sitemaster where branch='$branch'";
$result1 = mysql_query($sql1);
$num_results = mysql_num_rows($result1);
?>
<HTML><HEAD><TITLE>test page</TITLE></HEAD>
<BODY text=#666666 vLink=#cc6600 aLink=#cc6600 link=#cc6600 bgColor=#505438 topMargin=0 marginheight="10" marginwidth="10">
<TABLE cellSpacing=0 cellPadding=1 width=100% align=center border=0>
<TBODY><TR>
<TD class="a" height="350" vAlign=center bgColor=#000000>
<TABLE cellSpacing=0 cellPadding=0 width=100% border=0>
<TBODY><TR>
<TD class="a" bgcolor="#FFFFFF"><IMG height=60 src="graphics/alturalogo.jpg" width=175 border=0></TD>
<TD class="a" bgcolor="#FFFFFF"><IMG height=60 src="graphics/top.gif"></TD>
<TD class="a" bgcolor="#FFFFFF"><IMG height=60 src="graphics/line.gif" width=13></TD>
<TD class="a" vAlign="center" width="112" background="graphics/back.gif" height="60"> &nbsp; </TD>
<TD class="a" height=60 align="right" vAlign="middle" bgcolor="#FFFFFF"><IMG src="graphics/axis.gif">&nbsp;&nbsp;&nbsp;</TD></TR>
<TR bgcolor="#333333">
<TD class="a" colSpan=6>&nbsp;</TD></TR>
<TR>
<TD class="a" height="280" colSpan=6 vAlign=top bgColor=#ffffff>
<TABLE cellSpacing=0 cellPadding=0 width="100%" border=0>
<TBODY><TR>
<TD class="a" vAlign=top width=215>
<BR>
<TABLE width=90% align=center>
<TBODY><TR>
<TD class="a" valign="top" class="a"><div align="justify"></div></TD></TR></TBODY></TABLE></TD>
<TD class="a" vAlign=top width=100%>
<table width="70%" border="0" align="center">
<tr>
<td class="a">User Name : <?=$login?></td>
<td class="a"> Last Name : </td>
</tr>
<tr>
<td class="a">Access Level : <?=$alevel?></td>
<td class="a">First Name : </td>
</tr>
<tr>
<td class="a">Location : <?=$branch?></td>
<td class="a">Region : <?=$region?></td>
</tr>
</table>
<ul id="tabnav">
<li><a href="#" class="active">S I T E S</a></li>
<li><a class="five" href="contacts.php?login=<?=$login?>&pwd=<?=$pwd?>">C O N T A C T S</a></li>
<li><a href="#">Q U E R Y</a></li>
<li><a href="#">R E P O R T S</a></li>
</ul>
<table ID="table0" width='100%' border='1' align="center" bordercolor='black' style='border: 1px'>
<tr style="font-size= 60%; font-family= Verdana">
<td height="10"><div align='center'><strong><font color='#666666'>Site #</font></strong></div></td>
<td><div align='center'><font color="#666666"><strong>Site Status</strong> </font></div></td>
<td><div align='center'><strong><font color="#666666">Company Name</font></strong></div></td>
<td><div align='center'><strong><font color='#666666'>City</font></strong></div></td>
<td><div align='center'><strong><font color='#666666'>State</font></strong></div></td>
<td><div align='center'><strong><font color='#666666'>Group Code</font></strong></div></td>
</tr>
<?
for($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result1);
$siteno=$row[22];
$cname=$row[1];
$sstatus=$row[0];
$scity=$row[4];
$cstate=$row[5];
$gcode=$row[8];
?>
<tr style='font-size= 60%; font-family= Verdana'>
<td height='10'><div align="center"><a class="five" href="sdetail.php?siteno=<?=$siteno?>&cname=<?=$cname?>&sstatus=<?=$sstatus?>&scity=<?=$scity?>&cstate=<?=$cstate?>&gcode=<?=$gcode?>"><font color='#EFA61F'><?=$siteno?></font></div></td>
<td align="center"><font color='#666666'><?=$sstatus?></font></div></td>
<td align="center"><font color='#666666'><?=$cname?></font></div></td>
<td align="center"><font color='#666666'><?=$scity?></font></div></td>
<td align="center"><font color='#666666'><?=$cstate?></font></div></td>
<td align="center"><font color='#666666'><?=$gcode?></font></div></td>
</tr>
<?
}
?>
</table>
</TD></TR></TBODY></TABLE>
</TD></TR></TBODY></TABLE>
</TD></TR></TBODY></TABLE>
<BR></BODY></HTML>

Posted: Fri Sep 26, 2003 8:58 pm
by McGruff
The basics of a pagination script are:

(1) find the total number of rows in the table
(2) use that along with max per page and the selected page number to calculate the offset for a mysql LIMIT query (I'm assuming you are using mysql)
(3) create a page nav link: << next | previous >> or 1 | 2 | 3 - whatever you like.

Just some simple arithmetic really.