Prev / Next buttons - why do you all use such complex code?!?!?! - here is simple way

[rupertbj]

I've been struggling to get some previous and next buttons to work recently. I finally cracked it though.

I was looking for a simple solution - and there is always a simple solution - but was bombarded by long complex (at least for me) code involving counting and limit and god knows what else.

I'm going to share my code with you guys so that others don't have to suffer the same problems as me!

<?php
include("header.inc");
	$db = "gallery";
	$link = mysql_connect( "user", "pass" );
	if ( !$link ) die( "Couldn't connect to MySQL" );
	mysql_select_db( $db, $link ) or die( "Couldn't open $db: ".mysql_error() );
	$sqlnext = "SELECT * FROM galleryTable Where tour = '$page' AND id < '$id' ORDER BY id DESC";
	$sqlprev = "SELECT * FROM galleryTable Where tour = '$page' AND id > '$id' ";
	$resultnext = mysql_query( $sqlnext, $link );
	$resultprev = mysql_query( $sqlprev, $link );
?>
<p>&nbsp;</p>
<table width="80%" border="0" align="center" cellpadding="0" cellspacing="0">
  <tr> 
    <td width="10%" valign="middle" class="subtitle"><div align="center"></div></td>
    <td width="120%" align=center><?php print "<img src=\"/images/archive/$fupload\">"; ?></td>
    <td width="10%" valign="middle" class="subtitle"><div align="center"></div></td>
  </tr>
  <tr> 
    <td width="10%">&nbsp;</td>
    <td width="80%"><table width="80%" border="0" align="center" cellpadding="0" cellspacing="0">
        <tr> 
          <td width="50%" valign="top">Tour:</td>
          <td width="50%"><?php print " $page "; ?></td>
        </tr>
        <tr> 
          <td width="50%" valign="top">Name:</td>
          <td width="50%"><?php print " $name "; ?></td>
        </tr>
        <tr> 
          <td width="50%" valign="top">Materials:</td>
          <td width="50%"><?php print " $materials "; ?></td>
        </tr>
        <tr> 
          <td width="50%" valign="top">Size:</td>
          <td width="50%"><?php $size = stripslashes($size); print " $size "; ?></td>
        </tr>
        <tr> 
          <td width="50%" valign="top">Status:</td>
          <td width="50%"><?php print " $status "; ?></td>
        </tr>
        <tr> 
          <td width="50%" align="center" class="subtitle"> 
          <?php
		  while ( $newArray = mysql_fetch_array($resultprev) )
		  {
		  $id = $newArray['id'];
		  $status = $newArray['status'];
		  $name = $newArray['name'];
		  $materials = $newArray['materials'];
		  $size = $newArray['size'];
		  $size = htmlspecialchars($size);
		  $fupload = $newArray['fupload'];
		  $tupload = $newArray['tupload'];
		  print "
		  <a href=\"display.php?id=$id&fupload=$fupload&name=$name&materials=$materials&status=$status&size=$size&page=$page\">previous</a>
          </td>
		  ";
		  break;
		  }
		  while ( $newArray = mysql_fetch_array($resultnext) )
		  {
		  $id = $newArray['id'];
		  $status = $newArray['status'];
		  $name = $newArray['name'];
		  $materials = $newArray['materials'];
		  $size = $newArray['size'];
		  $size = htmlspecialchars($size);
		  $fupload = $newArray['fupload'];
		  $tupload = $newArray['tupload'];
		  print "
          <td width=\"50%\" align=center class=subtitle> 
		  <a href=\"display.php?id=$id&fupload=$fupload&name=$name&materials=$materials&status=$status&size=$size&page=$page\">next</a>
		  ";
		  break;
		  }
		  mysql_close( $link );	
		  ?>
          </td>
        </tr>
      </table></td>
    <td width="10%">&nbsp;</td>
  </tr>
</table>
<p>&nbsp;</p>
<?php
include("footer.inc");		
?>

I set up two arrays and two mysql queries for each array. depending if you press next or prev the next or previous record displays. Also, if there isn't another record then the next or previous button won't display.

This was so easy by comparison to the long scripts I've been reading!

[daveyboy]

that is pretty simple.

the thing about LIMIT is, you don't end up loading EVERY item from the database for every page and only showing 20 or so of them. your method DOES load every item, every page.

as the table increases in size, this may become a significant factor.

[rupertbj]

Thanks!

Bur could I not just add a LIMIT clause at the end of the mysql query anyway?

e.g.

	$sqlnext = "SELECT * FROM galleryTable Where tour = '$page' AND id < '$id' ORDER BY id DESC LIMIT 1";

Or would that not have the desired effect?

[daveyboy]

yes, i should think that would solve the problem.

[batman]

Here is my pagination code:

// limit value for number of records to be shown per page 
// query database to find total number of records to display 
$limit = 5; 
$query_count = "select * from ".$prefix."store_inventory where (description like '%$search%' or title like '%$search%')"; 
$result_count = mysql_query($query_count); 
$totalrows = mysql_num_rows($result_count); 
if(empty($page)) 
$page = 1; 
$limitvalue = $page * $limit - ($limit); 
$query = "select * from ".$prefix."store_inventory where (description like '%$search%' or title like '%$search%') LIMIT $limitvalue, $limit "; 
$result = mysql_query($query) or die("Error: " . mysql_error()); 
$count_result = mysql_num_rows($result); 

// Display links at the top to indicate current page and number of pages displayed
$numofpages = ceil($totalrows / $limit);
echo"<br><p><b>$totalrows</b> result(s) for: \"$search\"</p>";
$from=$limit*$page-$limit+1;
$to=$from + $count_result-1;

// start 123 next>> table
echo "<table align=\"center\" width=\"90%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">
            <tr><td width=\"50%\" bgcolor=\"$bg_colour\" align=\"left\">";if($numofpages>1){echo"Showing: $from - $to</td><td width=\"50%\" bgcolor=\"$bg_colour\" align=\"right\"><b>Go to page:</b> ";} 

// display previous link if page is not 1
if($page != 1){ 
$pageprev = $page - 1; 
echo("<a href=\"$PHP_SELF?page=$pageprev&search=$search\"><< PREV</a>&nbsp;"); 
} 

// display page nos if not 1 
for($i = 1; $i <= $numofpages; $i++){ 
if($numofpages>1){
if($i == $page) 
echo("&nbsp;".$i."&nbsp;"); 
else
echo("&nbsp;<a href=\"$PHP_SELF?page=$i&search=$search\">$i</a>&nbsp;"); 
}}

// display next page link if there is more than one
if(($totalrows - ($limit * $page)) > 0){ 
$pagenext = $page + 1; 
echo("<a href=\"$PHP_SELF?page=$pagenext&search=$search\">NEXT >></a>"); 
}
// end 123 next>> table
echo"</td></tr></table><br>";

Real easy to use.
🙂

[tiburblium]

Batman I really like your code, it was relitivly easy for me to understand and how it basically works!.I have been messing around with it trying to get it to print out only 10 result quick links...like google does.

so after a query of 150 records you would get:

<< previous 1 2 3 4 5 6 7 8 9 10 Next >>

And if you click on link 5 your link bar would be restructured to lok like this:

<< Previous 5 6 7 8 9 10 11 12 13 14 Next >>

Do you have any ideas or insite? They would be most appricated, thanks alot!!!!

[Zack]

tiburblium, do you think you could post it when you get it?

[tiburblium]

Definatly

[inthestars]

Batman, that is the best code ever!!! Thanks..I was up till 5 am last night trying to find and create good pagination code...

[DeltaRho2K]

Hey Batman, you should submit your code to phpfreaks.com... Their pagination code is all wrong in their tutorial section...

[batman]

lol, glad it works for you guys and that you all like it. I'm sad to say though that it's not all of my own codeing. You guys are welcome to it but I can't sell it in any way. I can probably give it to phpfreaks.com (It's the least I could do. I use a lot of their stuff. 🙂 )
Anyways...tiburblium post your code that you are using up to this point, and I'll see what I can do.

[tiburblium]

<?php
require'servars.php';

if(!isset($start)) $start = 0;

$connect = mysql_connect("$dbhost", "$dbuser", "$dbpass");
mysql_select_db($dbname);
$query = "SELECT * FROM classified LIMIT " . $start . ", 10";

// Find totals
$query2 = "SELECT * FROM classified";
$result2 = mysql_query($query2);
$rowcount2 = mysql_num_rows($result2);

$result = mysql_query($query);
$rowcount = mysql_num_rows($result);

if ($rowcount >= 1)
{
$ender = $start + 10;
$begin = $start + 1;
include ("adhead.php");
if ($ender >= $begin)
{
print "<h5>Viewing $begin - $rowcount2 of $rowcount2 Results</center></h5>";
}
else
{
print "<h5>Viewing $begin - $ender of $rowcount2 Results</center></h5>";
}
print "<table border=0 width=135% cellspacing=0 cellpadding=0>";
print "<TR>";
$colorshift = white;
$track = 0;
while ($row = mysql_fetch_array ($result))
{

	print "<table border=0 width=135% cellspacing=0 cellpadding=0>";
    	print "<TR>";

	if ($colorshift == white)
	{
		$color = ffffff;
		$colorshift = gray;
	}
	else
	{
		$colorshift = white;
		$color = eeeeee;
	}

	   	print "output"
}

// Generate page links for 1 2 3 4 5 6 7 8 9 10
for($i = 1; $i <= $numofpages; $i++)
{

if($numofpages>1)
{
if($i == $start)
{
echo("&nbsp;".$i."&nbsp;");
}

else
{
echo("&nbsp;<a href=\"$PHP_SELF?page=$i&search=$search\">$i</a>&nbsp;");

}
}
}

// generate prev/next buttons
$query = "SELECT count(*) as count FROM classified";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$numrows = $row['count'];

if($start > 0)
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start - 10) .
"\"><< Previous</a>\n";
if($numrows > ($start + 10))
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start + 10) .
"\">| Next >></a><BR>\n";

}

else
{
include ("adhead.php");
print "error output";

}
include("footer.php");

mysql_free_result ($result);
?>

[batman]

Ok cool. I'll take a look at it when I get home. I am at work right now on my lunch. 🙁

[leatherback]

Hi,

Instead of:

// Generate page links for 1 2 3 4 5 6 7 8 9 10 
for($i = 1; $i <= $numofpages; $i++) 

try:

$lastpage = $numofpages + $start;

// Generate page links for 1 2 3 4 5 6 7 8 9 10 
for($i = $start; $i <= $lastpage; $i++) 

Untested, but have faith, and it will work?

J.

[leatherback]

Oh,

You need to add a line which will test for the maximum page as well before creating the links, something like:

if($lastpage >$totalpages)
 {
 $lastpage  = $totalpages;
 }

.

[Zack]

there is a problem with your code... If you are incrementing by 10 and there are 67 results, your last page will still say:

"Viewing 61 - 70 of 67 Results"

I'll try to figure out a way to fix this, but if you do, post it before me...

[Zack]

I got it!

Change this:

if ($ender >= $begin) 
{ 
print "<h5>Viewing $begin - $rowcount2 of $rowcount2 Results</center></h5>"; 
} 
else 
{ 
print "<h5>Viewing $begin - $ender of $rowcount2 Results</center></h5>"; 
} 

To this:

if($ender > $rowcount2) {
print "<center><h5>Viewing $begin - $rowcount2 of $rowcount2 Results</center></h5>"; 
}

elseif ($ender >= $begin) 
{ 
print "<center><h5>Viewing $begin - $ender of $rowcount2 Results</center></h5>"; 
} 

else 
{ 
print "<center><h5>Viewing $begin - $ender of $rowcount2 Results</center></h5>"; 
} 

That way, on the last page, it will look like this:

Viewing 71 - 78 of 78 Results

Instead of this:

Viewing 71 - 80 of 78 Results

[Zack]

I also changed this (towards the bottom):

else 
{ 
include ("adhead.php"); 
print "error output"; 

} 

To this:

else 
{ 
include ("adhead.php"); 
header("Location: ". $PHP_SELF . "?start=0");

} 

That way instead of getting an error, you will be taken back to the beginning. For example, if your $start variable is 6 (you will be viewing 7 through 16 in a loop of 10), when clicking on previous, you will be taken back to the beginning (viewing 1 through 10) instead of recieving a dirty error...

[tiburblium]

I think I am getting really close here, thanks for the input I combined it in with what I had. Now I get the links generated correctly But.... mabey i am doing something wrong with the start variable because the 1 2 3 4 5 6 7 8 9 10 links dont carry me anywhere unforutunatly. Ill keep craking at it, sooner or later it should work!

Code:

<?php

if(!isset($start)) $start = 0;

$connect = mysql_connect("$dbhost", "$dbuser", "$dbpass");
mysql_select_db($dbname);
$query = "SELECT * FROM classified LIMIT " . $start . ", 10";

// Find totals
$query2 = "SELECT * FROM classified";
$result2 = mysql_query($query2);
$rowcount2 = mysql_num_rows($result2);

$result = mysql_query($query);
$rowcount = mysql_num_rows($result);

if ($rowcount >= 1)
{
$ender = $start + 10;
$begin = $start + 1;
include ("adhead.php");
if ($ender >= $begin)
{
print "<h5>Viewing $begin - $rowcount2 of $rowcount2 Results</center></h5>";
}
else
{
print "<h5>Viewing $begin - $ender of $rowcount2 Results</center></h5>";
}

print "<table border=0 width=135% cellspacing=0 cellpadding=0>";
    print "<TR>";
$colorshift = white;
$track = 0;
while ($row = mysql_fetch_array ($result)) 
{

	print "<table border=0 width=135% cellspacing=0 cellpadding=0>";
    	print "<TR>";

	if ($colorshift == white)
	{
		$color = ffffff;
		$colorshift = gray;
	}
	else
	{
		$colorshift = white;
		$color = eeeeee;
	}

	   	print "<td width=20% bgcolor=#$color>";
                    print "<img src=public/images/$row[picture].jpg width=75 height=75>";
		print "</td>";
                    print "<td width=20% bgcolor=#$color>";
                    print "<p align=center><font size=2 color=#0000FF><b><a href=$row[id].html><font 

color=#0000ff>$row[name]</b></font></p>";
print "</td>";
print "<td width=20% bgcolor=#$color>";
print "<p align=center><font size=2 color=#0000FF><b>$row[catagory]</b></font></p>";
print "</td>";
print "<td width=20% bgcolor=#$color>";
print "<p align=center><font size=2 color=#0000FF><b>$row[price]</b></font></p>";
print "</td>";
print "<td width=20% bgcolor=#$color>";
print "<p align=center><font size=2 color=#0000FF><b>$row[date]</b></font></p>";
print "</td>";

	print "</tr>";
	print "</table>";
	$track++;

}

print "<TABLE BORDER=0 WIDTH=135%>";
print "<TR>";
print "<TD ROWSPAN=2 WIDTH=200>";
print "</TD>";
print "<TD>";
print "</TD>";
print "</TR>";
print "<TR><TD ALIGN=left>";
print "<br>";
print "</A>";
print "</TABLE>";

if($lastpage >$totalpages)
{
$lastpage = $totalpages;
}

$lastpage = 10 + $start;

// Generate page links for 1 2 3 4 5 6 7 8 9 10
for($i = $start; $i <= $lastpage; $i++)
{

	if($i == $start) 
	{ 
		echo(" ".$i." "); 
	}

	else 
	{ 
		echo(" <center><font color=0000ff><b><a 

href=\"$PHP_SELF?page=$i&$start=$start+10\">$i</font></b></a></center> ");
}

}
print "<br><Br>";

$query = "SELECT count(*) as count FROM classified";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$numrows = $row['count'];

if($start > 0)
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start - 10) .
"\"><center><font color=0000ff><b><< Previous</b></font></a>\n";
if($numrows > ($start + 10))
echo " <b>|</b> ";
echo "<a href=\"" . $PHP_SELF . "?start=" . ($start + 10) .
"\"><font color=0000ff><b>Next >></b></a><BR>\n";

}

else
{
include ("adhead.php");
print "<TABLE BORDER=0 WIDTH=400>";
print "<TR>";
print "<TD ROWSPAN=2 WIDTH=100>";
print "</TD>";
print "<TD>";
print "</TD>";
print "</TR>";
print "<TR><TD ALIGN=center>";
print "<h3>No records matching your information were found<h3>";
print "</A>";
print "</TABLE>";
}
include("footer.php");

mysql_free_result ($result);
?>

[tiburblium]

Hmmm:

if($lastpage >$rowcount)
{
$lastpage = $rowcount;
}

$lastpage = 10 + $start;

// Generate page links for 1 2 3 4 5 6 7 8 9 10
for($i = $start; $i <= $lastpage; $i++)
{

	if($i == $start) 
	{ 
		echo(" ".$i." "); 
	}

	else 
	{ 
		echo"<a href=\"" . $PHP_SELF . "?start=" . ($start + $i) . 
    "\"><font color=0000ff><b> $i </b></a>\n";
	} 

}