page numbering system

justinb

hello All,
I need a page numbering system for PHP, I have some code that displays my db results and it works fine, All the tutorials I have found are like 3 pages and super advanced and i can't make heads or tails of them, ( 1 have one for ASP and its 10 lines of code) This is my code, if someone can help me modify it to add page numbering for all results over 5 ( or a EASY tutorial ) I would be very grateful ( it doesn't have to be fancy)

$sql = "SELECT * FROM $table_name WHERE catID = '$_REQUEST[id]' AND active = '1' ORDER BY busName ";

$result = @($sql, $connection) or die("Error3 - ".mysql_error());

while($row = mysql_fetch_array($result)) {

$busID = $row['busID'];
$catID = $row['catID'];
$busName = $row['busName'];
$busEmail = $row['busEmail'];
$busLogo = $row['busLogo'];
$busPhone = $row['busPhone'];
$busUrl = $row['busUrl'];

$contact_list .= "<tr><td width='150' align='center' rowspan='2'>";
if($busLogo != "") { $contact_list .= "<img src='logo/$busLogo'>";}
$contact_list .= "</td>";
$contact_list .= "<td colspan='2' valign='top'><b>$busName</b></td></tr><tr>";
$contact_list .= "<td valign='top'><b>Email: </b><a href='mailto:$busEmail' class='buttons'>$busEmail</a><br><b>Tel: </b> $busPhone<br></td><td valign='top'>";
if($busUrl != "") { $contact_list .= "<b>Website : </b><a href='http://www.$busUrl' class='buttons' target='blank'>$busUrl</a> <br>";}
$contact_list .= "<a href='businessDetails.php?id=$busID' class='buttons'>More Details.....</a></td></tr>";
$contact_list .= "<tr><td colspan='3'><hr width='75%' color='#cccccc'></td></tr>";
} // end while

cgraz

Hey, I posted some code a week or so back on how to do this. I had a hell of a time trying to figure this out for the first time, even with searching through forums and online tutorials, I couldn't get it to work.

Although the guy never responded, the link to the post is http://www.phpbuilder.com/board/showthread.php?s=&threadid=10219415 It's all commented so it should be pretty easy for you to follow and get working with your code.

There are also some other good threads on this topic in this forum. Post back if you need any more help.

Cgraz

justinb

thanks cgraz
that looks really easy to figure out, I'll post my success when I have a chance to try it.

justinb

hello,
thanks again, I am having problems with the code, I am getting no results a blank page (even when I view source) I tested it by hiding everything but the connection and it worked. Could u possible help me troubleshoot this.

$db_name = "xxxxxxxx";

$table_name = "business";

$connection = @mysql_connect("xxxxxx", "xxxxxx", "xxxxx") or die("Error1 - ".mysql_error());

$db = @mysql_select_db($db_name, $connection) or die("Error2 - ".mysql_error());

$pagelimit="1";

__$sql=mysql_query("SELECT * FROM $table_name WHERE catID = '$REQUEST[id]' AND active = '1'");

count number of matches

__$totalrows=_mysql_num_rows($sql);

__$pagenums=ceil($totalrows/$pagelimit);

_if($page==''){
___$page='1';
_}

___$start=($page-1)*_$pagelimit;

echo"<b>".$totalrows._" matches found</b><br>\n";

$starting_no=$start+1;

if($totalrows-$start<$pagelimit){
$end_count=$totalrows;
}elseif($totalrows-$start>=$pagelimit){
$end_count=$start+_$pagelimit;
}

echo"Results$starting_no_to_$end_count_shown.<br>\n";

if($totalrows-$end_count>$pagelimit){
$var2=$pagelimit;
}elseif($totalrows-$end_count<=$pagelimit){
$var2=$totalrows-$end_count;
}
$space=_" ";

if($page>1){
_____echo"« <a href='showCategory.php?page=".($page-1)."' class=main>Previous".$space.$pagelimit."</a>".$space.$space."";
__}

_for($i=1;$i<=$pagenums;$i++){
_if($i!=$page){
______echo"<a_href='showCategory.php?page=$i'class=main>$i</a>";<br>
___}
__else{
______echo"<b_class='red'>$i</b>";
__}
_}

_if($page<$pagenums){
__echo"".$space.$space.$space.$space." <a href='showCategory.php?page=".($page+1)."' class=main>Next ".$var2."</a> »";
__}

// $sql = "SELECT * FROM $table_name WHERE catID = '$_REQUEST[id]' AND active = '1' ORDER BY busName ";

$sql = "SELECT * FROM $table_name WHERE catID = '$REQUEST[id]' AND active = '1' ORDER BY busName LIMIT$start,$pagelimit ";

$result = @($sql, $connection) or die("Error3 - ".mysql_error());

while($row = mysql_fetch_array($result)) {

$busID = $row['busID'];
$catID = $row['catID'];
$busName = $row['busName'];
$busEmail = $row['busEmail'];
$busLogo = $row['busLogo'];
$busPhone = $row['busPhone'];
$busUrl = $row['busUrl'];

?>
<? include ("headerTop.php"); ?>
<? include ("buttons.php"); ?>
<table width="100%" height="381" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="150" valign="top">
<? include ("sideButtons.php"); ?>
</td>
<td height="381" align="center" valign="top"> <div align="center"><img src="images/viewBusiness.gif" width="400" height="37">
</div>
<table width="100%" border="0" cellpadding="2" cellspacing="2">

<? echo "$contact_list" ?>
</table>

  </td>
<td width="150" height="381" valign="top">

<? include ("advertisments.php"); ?></td>
</tr>
</table>
<table width="100%" height="70" border="0" cellpadding="0" cellspacing="0">
<tr>
<td><? include ("footer.php"); ?></td>
</tr>
</table>
</body>
</html>

justinb

Ok, I got got going mostly, but it is a bit buggy, It is set for 2 returns now, but if their are 2 returns exactly, it will display a button for page 2 which is blank. one more thing, I can't successfully display the word "page" before it, when I try it will say
page 1 page 2 page 3
i need it to say page 1 2 3
now it just says
1 2 3

$db = @mysql_select_db($db_name, $connection) or die("Error2 - ".mysql_error());

if(!$page_num || $page_num == "")
{
$page_num = 1;
}

$min = ($page_num - 1) 2;
$max = ($page_num 2) - 1;
$addquery = " LIMIT $min, 2";

$current_page = $page_num;

$sql = "SELECT FROM $table_name WHERE catID = '$REQUEST[id]' AND active = '1' ORDER BY busName $addquery";
$sql3 = "SELECT FROM $table_name WHERE catID = '$REQUEST[id]' AND active = '1' ";

$result = @($sql, $connection) or die("Error 2 - PLEASE TRY LATER".mysql_error());

$result3 = @($sql3, $connection)
or die("Error3 - ".mysql_error());

$total_items = @mysql_num_rows($result3);
$page_num = ($total_items / 2) + 1;

while($row = mysql_fetch_array($result)) {

$busID = $row['busID'];
$catID = $row['catID'];
$busName = $row['busName'];
$busEmail = $row['busEmail'];
$busLogo = $row['busLogo'];
$busPhone = $row['busPhone'];
$busUrl = $row['busUrl'];

?>
<?
for($i=1;$i<=$page_num;$i++)
{
if($i != $current_page)
echo "<a href=\"showCategory.php?id=$_REQUEST[id]&page_num=$i\">$i</a> ";
else
echo "";
}

?>
</td></tr>

<? echo "$contact_list" ?>
</table>

cgraz

You need to run your select query first without the limit to get a page count, etc, then run it again later with a limit.

Try changing

$sql = "SELECT * FROM $table_name WHERE catID = '$_REQUEST[id]' AND active = '1' ORDER BY busName $addquery";

$sql = "SELECT * FROM $table_name WHERE catID = '$_REQUEST[id]' AND active = '1'"

then right before your while statment but before $page_num = ($total_items / 2) + 1; , run the query again with limit.

$sql = "SELECT * FROM $table_name WHERE catID = '$_REQUEST[id]' AND active = '1' ORDER BY busName $addquery"; 
$limit_result = mysql_query($sql);

// now run your while statment and everything below
// note it's fetching the array for $limit_result now
while($row = mysql_fetch_array($limit_result)) { 
// rest of code here

Cgraz

justinb

thanks, i tried it, it makes no differnce, it is still displaying an extra page link even if their are no more reults to get(i changed it to 5, thats how many in db)

$sql3 = "SELECT * FROM $table_name WHERE catID = '$_REQUEST[id]' AND active = '1' ";

$result3 = @($sql3, $connection)
or die("Error3 - ".mysql_error());

$total_items = @mysql_num_rows($result3);

$sql = "SELECT * FROM $table_name WHERE catID = '$_REQUEST[id]' AND active = '1' ORDER BY busName $addquery";
$limit_result = @($sql, $connection) or die("Error 2 - PLEASE TRY LATER".mysql_error());
$page_num = ($total_items / 5) + 1;

while($row = mysql_fetch_array($limit_result)) {

justinb

any ideas