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

**HaLo2FrEeEk** · Feb 7 '11, 07:59 AM

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.

Code:

$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** · Feb 8 '11, 10:41 PM

reply to reply

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

Code:

$_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** · Feb 9 '11, 12:35 AM

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:

Code:

$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:

Code:

// 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.

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

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

Comment

Comment

Comment