By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,677 Members | 1,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,677 IT Pros & Developers. It's quick & easy.

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

P: 2
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.php";

$_GET['out']=strip_tags($_GET['out']);
$_GET['out']=mysql_real_escape_string($_GET['out']);
if($_GET['out']!="")
{
mysql_query("update offers set visits=visits+1 where id={$_GET['out']}",$c);
$geturl=mysql_query("select * from offers where id={$_GET['out']}",$c);
$out=mysql_fetch_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.php";
include"footer.php";
exit;
}
else
{
$money=0;
$getoffercash=mysql_query("select * from offers where active=1",$c);
while($oc=mysql_fetch_array($getoffercash))
{
$money=$money+$oc['reward'];
}
}
if($_SESSION['loggedin']==1)
{
$refer="<br>Refer 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?ref=<? print"$ref"; ?>">not sure what to do?</a></p>
<p><center>Possible Earnings: $<?=$money;?><?=$refer;?></center></p>
<table width="90%">
<tr>
<th colspan="0" align="center">
Free To Complete
</th>
</tr>
<tr>
<th width="25%">Offer</th>
<th width="10%">Date</th>
<th width="45%">Description</th>
<th width="10%">Reward</th>
<th width="10%">Completed?</th>
</tr>
<?

$_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",$c);

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

while($off=mysql_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=\"searchform2\" action=\"completed.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=mysql_query("select * from pending where offer_id={$off['id']} and user_id='{$_SESSION['userid']}' and denied=0",$c);
$checkcompleted=mysql_query("select * from completed where offer_id={$off['id']} and user_id='{$_SESSION['userid']}'",$c);

if(mysql_num_rows($checkpend)!=0)
{
$form="<font color=\"orange\"><b>Pending</b></font>";
}
else if(mysql_num_rows($checkcompleted)!=0)
{
$form="<font color=\"green\"><b>Completed</b></font>";
}

if(mysql_num_rows($checkpend)!=0 || mysql_num_rows($checkcompleted)!=0)
{
$link="$front{$off['name']}$back";
}
else
{
$link="<a href=\"offers.php?out={$off['id']}\" target=\"_blank\" title=\"Available in: {$off['countries']}\">$front{$off['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.php";
include"footer.php";
?>
Feb 6 '11 #1
Share this Question
Share on Google+
3 Replies


HaLo2FrEeEk
100+
P: 404
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.

Expand|Select|Wrap|Line Numbers
  1. $rpp = 20;
  2. $page = @$_GET['page'];
  3. if(!$page) {
  4.   $page = 0;
  5.   }
  6.  
  7. $sql = mysql_query("SELECT * FROM `table` LIMIT ".($rpp * $page).", ".$rpp) or die("Error: " . mysql_error());
  8. // Do something with your results
  9.  
  10. $sql = mysql_query("SELECT count(*) FROM `table`") or die("Error: " . mysql_error());
  11. $res = mysql_result($sql, 0);
  12. $totalpages = ceil($res / $rpp); // ceil() rounds fractions up to the next whole number
Hopefully I've given you enough to start with.
Feb 7 '11 #2

P: 2
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

Expand|Select|Wrap|Line Numbers
  1. $_GET['offer']=strip_tags($_GET['offer']);
  2. $_GET['offer']=mysql_real_escape_string($_GET['offer']);
  3. $type="free";
  4. $getoffers=mysql_query("select * from offers where `type`='$type' and active=1 order by reward desc LIMIT 20,30",$c);
  5. if (isset($_GET['pageno'])) {
  6.    $pageno = $_GET['pageno'];
  7. } else {
  8.    $pageno = 1;
  9. } // if
  10. $query = "SELECT count(*) FROM table WHERE ...";
  11. $result = mysql_query($query, $db) or trigger_error("SQL", E_USER_ERROR);
  12. $query_data = mysql_fetch_row($result);
  13. $numrows = $query_data[0];
  14. $rows_per_page = 25;
  15. $lastpage      = ceil($numrows/$rows_per_page);
  16. $pageno = (int)$pageno;
  17. if ($pageno > $lastpage) {
  18.    $pageno = $lastpage;
  19. } // if
  20. if ($pageno < 1) {
  21.    $pageno = 1;
  22. } // if
  23. $limit = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
  24. $query = "SELECT * FROM table $limit";
  25. $result = mysql_query($query, $db) or trigger_error("SQL", E_USER_ERROR);
  26. ... process contents of $result ...
  27. if ($pageno == 1) {
  28.    echo " FIRST PREV ";
  29. } else {
  30.    echo " <a href='{$_SERVER['PHP_SELF']}?pageno=1'>FIRST</a> ";
  31.    $prevpage = $pageno-1;
  32.    echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$prevpage'>PREV</a> ";
  33. } // if
  34. echo " ( Page $pageno of $lastpage ) ";
  35. if ($pageno == $lastpage) {
  36.    echo " NEXT LAST ";
  37. } else {
  38.    $nextpage = $pageno+1;
  39.    echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$nextpage'>NEXT</a> ";
  40.    echo " <a href='{$_SERVER['PHP_SELF']}?pageno=$lastpage'>LAST</a> ";
  41. } // if
  42.  
  43.  
  44.  
Feb 8 '11 #3

HaLo2FrEeEk
100+
P: 404
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:

Expand|Select|Wrap|Line Numbers
  1. $page = @$_GET['page']; // Gets the current page
  2. if(!$page)
  3.   $page = 0; // Defaults the pae to 0 if it's not provided.
  4. $rpp = 20; // This is the number of results per page
  5.  
  6. // This query sets the limit to start at the page number multipled by the results per page, and to get $rpp results.
  7. $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:

Expand|Select|Wrap|Line Numbers
  1. // You've already got your $page variable
  2. $sql = mysql_query("SELECT count(*) FROM `table`") or die("Error: ".mysql_error());
  3. $numrows = mysql_result($sql, 0);
  4. $numpages = ceil($numrows / $rpp);
  5.  
  6. if($page <= $numpages) {
  7.   echo "<a href=\"?page=".($page + 1)."\"><< Previous</a>";
  8.   }
  9. if($page > 0) {
  10.   echo "<a href=\"?page=".($page - 1)."\">Next >></a>";
  11.   }
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.
Feb 9 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.