How to display 20 results per page, with a next and previous page?

[christy jones]

I would like to add 20 offers per page and becuase the database is updated daily. i need a way to allow access to other pages so that all offers will be displayed. i was thinking the next / previous function, but my page count can constantly change so im wanting some way of it automatically only having the amount of pages needed to support the amount of offers. normally i can display anywhere from 100-1000+ offers


code:

<?
session_start() ;
$page="offers";
include"header. php";
include"mysql.p hp";

$_GET['out']=strip_tags($_G ET['out']);
$_GET['out']=mysql_real_esc ape_string($_GE T['out']);
if($_GET['out']!="")
{
mysql_query("up date offers set visits=visits+1 where id={$_GET['out']}",$c);
$geturl=mysql_q uery("select * from offers where id={$_GET['out']}",$c);
$out=mysql_fetc h_array($geturl );
print"
<h2>Please wait..</h2>
<p>
<center>
<meta http-equiv=\"refresh \" content=\"2;url ={$out['url']}/&subid1={$ui['username']}\">
Taking you to the offer...<br>
If this page does not change in 2 seconds, <a href=\"{$out['url']}/&subid1={$ui['username']}\">click here</a>.
</center>
</p>
";
include"side.ph p";
include"footer. php";
exit;
}
else
{
$money=0;
$getoffercash=m ysql_query("sel ect * from offers where active=1",$c);
while($oc=mysql _fetch_array($g etoffercash))
{
$money=$money+$ oc['reward'];
}
}
if($_SESSION['loggedin']==1)
{
$refer="<br>Ref er your friends: $siteurl/?ref={$ui['username']}<br>and receive an instant $$refbonus bonus per referral!";
}
else
{
$refer="";
}
?>


<h2>Free Offers</h2>
<p class="post-by"><a href="how.php?r ef=<? print"$ref"; ?>">not sure what to do?</a></p>
<p><center>Poss ible Earnings: $<?=$money;?><? =$refer;?></center></p>
<table width="90%">
<tr>
<th colspan="0" align="center">
Free To Complete
</th>
</tr>
<tr>
<th width="25%">Off er</th>
<th width="10%">Dat e</th>
<th width="45%">Des cription</th>
<th width="10%">Rew ard</th>
<th width="10%">Com pleted?</th>
</tr>
<?

$_GET['offer']=strip_tags($_G ET['offer']);
$_GET['offer']=mysql_real_esc ape_string($_GE T['offer']);
$type="free";
$getoffers=mysq l_query("select * from offers where `type`='$type' and active=1 order by reward desc",$c);

if(mysql_num_ro ws($getoffers)= =0)
{
print"<tr><td colspan=\"3\">T here are currently no free offers available</td></tr>";
}
else
{

while($off=mysq l_fetch_array($ getoffers))
{
$desc=$off['desc'];
$date=$off['date'];

if($_GET['offer']==$off['id'])
{
$color="style=\ "background-color: #a2e250;\"";
$front="<font color=\"black\" ><b>";
$back="</b></font>";
}
else
{
$color="";
$front="";
$back="";
}
if($_SESSION['loggedin']==1)
{
$form="<form class=\"searchf orm2\" action=\"comple ted.php\" method=\"get\"> <input type=\"hidden\" name=\"offer\" value=\"{$off['id']}\"><input type=\"submit\" class=\"button\ " value=\"Done!\" ></form>";
}
else
{
$form="<font color=\"red\">< b>Please Login</b></font>";
}


$checkpend=mysq l_query("select * from pending where offer_id={$off['id']} and user_id='{$_SES SION['userid']}' and denied=0",$c);
$checkcompleted =mysql_query("s elect * from completed where offer_id={$off['id']} and user_id='{$_SES SION['userid']}'",$c);

if(mysql_num_ro ws($checkpend)! =0)
{
$form="<font color=\"orange\ "><b>Pendin g</b></font>";
}
else if(mysql_num_ro ws($checkcomple ted)!=0)
{
$form="<font color=\"green\" ><b>Completed </b></font>";
}

if(mysql_num_ro ws($checkpend)! =0 || mysql_num_rows( $checkcompleted )!=0)
{
$link="$front{$ off['name']}$back";
}
else
{
$link="<a href=\"offers.p hp?out={$off['id']}\" target=\"_blank \" title=\"Availab le in: {$off['countries']}\">$front{$o ff['name']}$back</a>";
}

print"
<tr $color>
<td width=\"100%\"> $link</td>
<td align=\"center\ ">$date</td>
<td align=\"center\ ">$desc</td>
<td align=\"center\ ">$front\${ $off['reward']}$back</font></td>
<td align=\"center\ ">$form</td>
</tr>
";
}
}

?>
</table>
<?
include"side.ph p";
include"footer. php";
?>

[HaLo2FrEeEk]

First off, please use code tags in the future, they make code a lot easier to read on the forum because it preserves formatting.

As for your question, there are a few aspects. To get a limited set of results from the database use the LIMIT command in your query. Example:

SELECT * FROM `table` ORDER BY `id` DESC LIMIT 0, 20

This query would select the most recent 20 results. You need something to sort it by though, in this example I'm sorting by id, id would be a column set to auto increment, something where newer rows have a higher value than older rows.

Next, you need to count how many rows there are total, for this you could use the count() selector:

SELECT count(*) FROM `table`

This will allow you to calculate how many pages you need. For example, if you are displaying 20 results per page and you have 50 results in the table, then you need 3 pages. The first 2 pages would have 20 results and the last page would have 10. I'd recommend setting a variable with the number of results you want per page, you can use this variable in your first SELECT query, the one with the limit command, you can also use it in your calculations.

Finally, you need a GET variable that represents which page you're currently on.

$rpp = 20;
$page = @$_GET['page'];
if(!$page) {
  $page = 0;
  }
 
$sql = mysql_query("SELECT * FROM `table` LIMIT ".($rpp * $page).", ".$rpp) or die("Error: " . mysql_error());
// Do something with your results
 
$sql = mysql_query("SELECT count(*) FROM `table`") or die("Error: " . mysql_error());
$res = mysql_result($sql, 0);
$totalpages = ceil($res / $rpp); // ceil() rounds fractions up to the next whole number

Hopefully I've given you enough to start with.

[christy jones]

Ive added the Limit 20,30 to the command line, but im noticing that when i tried to change the values, the data is not showing the correct amt. i only see my first change of limit 0,20 and nothing else. ive also followed the guide for displaying pages and am not showing any links to the pages, im also receiving no errors. would be happy to provide link to site through an email

$_GET['offer']=strip_tags($_GET['offer']);
$_GET['offer']=mysql_real_escape_string($_GET['offer']);
$type="free";
$getoffers=mysql_query("select * from offers where `type`='$type' and active=1 order by reward desc LIMIT 20,30",$c);
if (isset($_GET['pageno'])) {
   $pageno = $_GET['pageno'];
} else {
   $pageno = 1;
} // if
$query = "SELECT count(*) FROM table WHERE ...";
$result = mysql_query($query, $db) or trigger_error("SQL", E_USER_ERROR);
$query_data = mysql_fetch_row($result);
$numrows = $query_data[0];
$rows_per_page = 25;
$lastpage      = ceil($numrows/$rows_per_page);
$pageno = (int)$pageno;
if ($pageno > $lastpage) {
   $pageno = $lastpage;
} // if
if ($pageno < 1) {
   $pageno = 1;
} // if
$limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
$query = "SELECT * FROM table $limit";
$result = mysql_query($query, $db) or trigger_error("SQL", E_USER_ERROR);
... process contents of $result ...
if ($pageno == 1) {
   echo " FIRST PREV ";
} else {
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
   $prevpage = $pageno-1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
} // if
echo " ( Page $pageno of $lastpage ) ";
if ($pageno == $lastpage) {
   echo " NEXT LAST ";
} else {
   $nextpage = $pageno+1;
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
   echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
} // if
 
 
 

[HaLo2FrEeEk]

I gave you the tools and knowledge you need to solve problem, I won't write the code for you. You need to go back and read my post.

the LIMIT command in a query looks like this:

LIMIT 0, 20

That means that thequery will get 20 results starting from the 0th (first) result, so it will get results 0-19. You need to use your page variable to determine what value you start from, so if you're on page 1, then your limit will look like it does above, if you're on page 2 then it will be LIMIT 20, 20, page 3, LIMIT 40, 20. Make sense? You calculate this by having a variable where you specify how many results you want to display per page:

$page = @$_GET['page']; // Gets the current page
if(!$page)
  $page = 0; // Defaults the pae to 0 if it's not provided.
$rpp = 20; // This is the number of results per page
 
// This query sets the limit to start at the page number multipled by the results per page, and to get $rpp results.
$sql = "SELECT * FROM `table` ORDER BY `reward` LIMIT ".($pae * $rpp)).", ".($rpp);

Please note that this isn't plug n' play code, it's an example that you will need to understand before you can use it.

As for the pages, here's a simple way to show a previous and next link:

// You've already got your $page variable
$sql = mysql_query("SELECT count(*) FROM `table`") or die("Error: ".mysql_error());
$numrows = mysql_result($sql, 0);
$numpages = ceil($numrows / $rpp);
 
if($page <= $numpages) {
  echo "<a href=\"?page=".($page + 1)."\"><< Previous</a>";
  }
if($page > 0) {
  echo "<a href=\"?page=".($page - 1)."\">Next >></a>";
  }

Once again, this is example code, you need to understand HOW it works and not just THAT it works, otherwise you won't learn anything and you'll be right back here the next time you have a problem like this. Understanding and learning why and how the code works is the first step to furthering your own knowledge and ability, it's how I've gotten as good as I am at this kind of thing.