Page 1 of 6

Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 4:43 am
by simonmlewis
Hi all

I have been looking into how to do this, but it all seems so damn complicated - there must be a simpler way that someone has come up with...

I am building an online shop, and one of the categories has around 50 items in it. Each item is shown as a thumbnail and I want to limit that 15 or 20 products per page, and at the bottom....yes... you guess it, Page 1, 2, 3......

I'm assuming it will be via loop, but cannot quite see how it would know from the 'next page' what was shown on the previous page.

Also, with this method, does it also put less workload on the server, by not actually grabbing all 50... or 1000 products at a time?

Hopefully someone is online on this (not fine) Sunday in the UK.

Regards
Simon

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 7:12 am
by Cross89
You're talking about pagination. Queries the database and limits the display to a number of your choice (20). Check this link out: http://www.sitepoint.com/article/perfec ... agination/

Hope that helps.

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 7:52 am
by Raph
What you're looking for is called paging, and can be as easy or as complicated as you make it. There are tons of tutorials on this subject, but I'm guessing you've read some and they didn't make much sense. I'll do my best to explain the logic behind paging.

When splitting hits (read: database-results) over several pages, you have to define two values. The values represent the interval of the hits that should be returned from the DB. You define via LIMIT $offset, $results where $offset is how many results you skip, and $results how many results you want. For instance, if you want to display the first page, and you want to display the first 20 results, the $offset is 0, and $results is 20. You use these two values in the DB-query, and intelligent as MySQL is, it returns the results. This works in a similar fashion for all the pages you want to display the results on. For the second page, you'd use LIMIT $offset, $results, where $offset is 20, and $results is still 20. MySQL skips the first 20 results, and displays the next 20 results. For page 3 the $offset is 40. I think you get the picture.

There is a second way to do this, if you don't like the first way. MySQL has, as you probably know, a nice feature called OFFSET, which tells MySQL how many results to skip before pumping out the results. For page 1 you'd use LIMIT $results OFFSET $offset, where $offset again is 0. For page 2 $offset would be 20, for page 3 it would be 40, and so on.

And how to made the system know what results to display on each page? That is where the tutorials come in, they all show ways to define them. Normally you define to display page 1 by default, but if a page is specifically asked for, you define the variable after the $_GET-value. For instance:

Code: Select all

// by default we show first page
$page = 1;
 
// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $page = $_GET['page'];
}
You can do the same with how many results per page you want to display, but that's beyond the basics. Maybe you've come across a forum or a site which allows you to choose how many resulsts / posts you wish to display per page via a drop-box. It's done in the same fashion as the code above.
The query that you use would be something in the lines of:

First way

Code: Select all

$sql = mysql_query("SELECT value FROM table LIMIT $offset, $resultsPerPage")
Second way

Code: Select all

$sql = mysql_query("SELECT value FROM table LIMIT $results OFFSET $offset")
In order to display the links to the appropriate pages, you count the values in table with count(), and use ceil($total_amount_of_rows_in_table / $results_per_page). If you have 20 results in total, that would equal to one page. If you have 30, that would equal to two pages. If you have 40, that would again equal to two pages.

Now that you know the amount of pages to display, you simply loop them out. You can use a while- or a for-loop. In the case of a while-loop, it can look something in the lines of:

Code: Select all

$page = 1;
$total_pages = ceil($total_amount_of_rows_in_db / $results_per_page)
while ($page <= $total_pages){
 echo "link here to $page ";
$page++;
}
You can decide to either use page.php?page=x, where x equals the number of the page, or you can use the $offset instead, with page.php?display=x, or something in a similar fashion. It's all down to your personal prefference.

Some reading material, which I used as a refference while I wrote this (two years since last time I wrote a paging system):
Paging using LIMIT $offset, $results_per_page
Paging using LIMIT $results_per_page OFFSET $offset

edit:
The offset value can be calculated with

Code: Select all

$offset = ($pageNum - 1) * $rowsPerPage;
For page 1, that would be 0 * 20 ( = 0 ), which means page 1 skips 0 results.
For page 2, that would be 1 * 20 ( = 20), which means page 2 skips 20 results.
etc.

All codes are based on, or copied from here.

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 8:01 am
by simonmlewis
Thanks to all for responding.

The first "pagination" option looks nice, but uses extra pages to be included.
The second one "paging" looks like less coding, but still not sure how it works with code like this, which I have in use right now:

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE category = '$mycateg' AND stockstatus= 'in stock' ORDER BY RAND() LIMIT 0, 12");
 
while ($row = mysql_fetch_object($result))
      {
            echo "
            <div class='cat_prodlistbox'>
            <div class='cat_producttitle'>";
                        $cookieuser = $_COOKIE['user'];
if (isset($_COOKIE['user'])) { echo "<div style='width:20px; float:right'>
<form method='post' action='index.php?page=a_productedit&menu=a_productshelp'>
<input type='hidden' name='id' value='$row->id'>
<input type='submit' value='Edit' class='submittext'>
</form></div>";}
            echo "$row->title</div>
            <a href='index.php?page=product&menu=categ&category=$mycateg&product=$row->id' title='Look at the $row->title'><img src='images/productphotos/$row->photoprimary' border='0' /></a><br/>";
        
            
            echo "£$row->price</div>
            
            ";
            }
    mysql_free_result($result);
 
You will notice the RANDOM code in the query - I did this for the time being to restrict the rendered products.

I can't quite see how the Paging code would fit in with this.... :(

But you are right, I have read so much about this, my brain is mashed. I can see the OFFSET idea, and I am assuming that passed from one page to another is the xx offset about to use and ignore.

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 8:20 am
by Raph
You're correct in your last statement. Regarding your code, I'd help you out to implement it, but sunday is the day when God gave us time to actually do stuff, so I've not enough time to help you implement it with your current code. However, I suggest you do what I do when my brain gets as cluttered as my code, start with a new (and EMPTY) page, and move over *essential* stuff only, trying to make the page as clean as possible without the extra bells and whistles. Try making a page with that only displays the selected page, the results from the db, and the page-links, and post it here, and I'll review it later today if noone else has beat me to it. IE, no form-, layout- or cookie-stuff. Just the pure core of your paging-system-to-be.

The first link I supplied is more of a complete tutorial from start to finnish, the second link is basically only explaining how to change the sql-queries from one format to another. The first guide explains everything else, albeit abit cluttered.

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 8:48 am
by simonmlewis

Code: Select all

<?php 
$mycateg = $_REQUEST['category'];
echo "<div class='categorytitle'>$mycateg</div>";
include "dbconn.php";
    
    // how many rows to show per page
$rowsPerPage = 20;
 
// by default we show first page
$pageNum = 1;
 
// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}
 
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
    
$query = " SELECT * FROM trimex_products WHERE category = '$mycateg' " .
         " LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');
 
while ($row = mysql_fetch_object($result))
      {
            echo "
            <div class='cat_prodlistbox'>
            <div class='cat_producttitle'>
            $row['title']</div>
            <a href='index.php?page=product&menu=categ&category=$mycateg&product=$row['id'] title='Look at the $row['title']><img src='images/productphotos/$row['photoprimary'] border='0' /></a><br/>£$row['price']</div>
            ";
            }
    
    $query   = "SELECT COUNT(id) AS numrows FROM trimex_products";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
    // how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
 
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';
 
for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"$self?page=$page\">$page</a> ";
   }
}
 
// creating previous and next link
// plus the link to go straight to
// the first and last page
 
if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";
 
   $first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
   $prev  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // nor the first page link
}
 
if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"$self?page=$page\">[Next]</a> ";
 
   $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
   $next = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
}
 
// print the navigation link
echo $first . $prev . $nav . $next . $last;
    mysql_close($sqlconn);
?>
Well this is now, stripped down. The "mycateg" must be here so it knows what category to look for in it's search. But that's hardly tasking to the code.

Result: absolutely nothing shown at all. No error - nothing.

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 9:10 am
by Raph
The problem is in your while-loop. Embed your $row[]'s with ". ." on line 30 and 31, and you should be syntax-problem free.

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 9:22 am
by simonmlewis
Like this?

Code: Select all

<?php 
$mycateg = $_REQUEST['category'];
echo "<div class='categorytitle'>$mycateg</div>";
include "dbconn.php";
    
    // how many rows to show per page
$rowsPerPage = 20;
 
// by default we show first page
$pageNum = 1;
 
// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}
 
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
    
$query = " SELECT * FROM products WHERE category = '$mycateg' " .
         " LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');
 
while ($row = mysql_fetch_object($result))
      {
            echo "
            <div class='cat_prodlistbox'>
            <div class='cat_producttitle'>
            . $row['title'] .</div>
            <a href='index.php?page=product&menu=categ&category=$mycateg&product= . $row['id'] . title='Look at the  . $row['title'] . ><img src='images/productphotos/ . $row['photoprimary'] . border='0' /></a><br/>£ . $row['price'] . </div>
            ";
            }
    
    $query   = "SELECT COUNT(id) AS numrows FROM trimex_products";
$result  = mysql_query($query) or die('Error, query failed');
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
    // how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
 
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';
 
for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"$self?page=$page\">$page</a> ";
   }
}
 
// creating previous and next link
// plus the link to go straight to
// the first and last page
 
if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";
 
   $first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
   $prev  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // nor the first page link
}
 
if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"$self?page=$page\">[Next]</a> ";
 
   $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
   $next = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
}
 
// print the navigation link
echo $first . $prev . $nav . $next . $last;
    mysql_close($sqlconn);
?>
The resulting page is still totally empty, with nothing rendered even in code.

Thanks for support so far.

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 9:46 am
by Raph
Actually, if not counting a few misses here and there, your system works perfectly. You'll have to change some table-names and such, but here's the corrected code.

Code: Select all

<?php
$mycateg = $_REQUEST['category'];
echo "<div class='categorytitle'>$mycateg</div>";
include "dbconn.php";
 
    // how many rows to show per page
$rowsPerPage = 20;
 
// by default we show first page
$pageNum = 1;
 
// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}
 
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
 
$result = mysql_query("SELECT * FROM table LIMIT $offset, $rowsPerPage") or die('Error, query failed');
 
while ($row = mysql_fetch_array($result))
      {
            echo "
            <div class='cat_prodlistbox'>
            <div class='cat_producttitle'>
            ". $row['title'] ."</div>
            <a href='index.php?page=product&menu=categ&category=$mycateg&product=". $row['id'] ." title='Look at the ". $row['title'] ."><img src='images/productphotos/". $row['photoprimary'] ." border='0' /></a><br/>£". $row['price'] ."</div>
            ";
            }
 
    $query   = "SELECT COUNT(id) AS numrows FROM table";
$result  = mysql_query($query) or die(mysql_error());
$row     = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
    // how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
 
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav  = '';
 
for($page = 1; $page <= $maxPage; $page++)
{
   if ($page == $pageNum)
   {
      $nav .= " $page "; // no need to create a link to current page
   }
   else
   {
      $nav .= " <a href=\"$self?page=$page\">$page</a> ";
   }
}
 
// creating previous and next link
// plus the link to go straight to
// the first and last page
 
if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"$self?page=$page\">[Prev]</a> ";
 
   $first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
   $prev  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // nor the first page link
}
 
if ($pageNum < $maxPage)
{
   $page = $pageNum + 1;
   $next = " <a href=\"$self?page=$page\">[Next]</a> ";
 
   $last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
   $next = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
}
 
// print the navigation link
echo $first . $prev . $nav . $next . $last;
    mysql_close($sqlconn);
?>

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 9:53 am
by simonmlewis
Error, query failed
The Category name at least comes on from the previous page, but nothing else, and I have changed "table" to be the table name.

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 9:57 am
by simonmlewis
It's this part that fails.... but not sure why:

Code: Select all

$result = mysql_query("SELECT * FROM products WHERE category = $mycateg LIMIT $offset, $rowsPerPage") or die('Error, query failedd');

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 9:59 am
by Raph
Can you change the

Code: Select all

or die('Error, Query failed"');

to:

Code: Select all

or die (mysql_error());
And copy the error message.

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 10:02 am
by Raph
simonmlewis wrote:It's this part that fails.... but not sure why:

Code: Select all

$result = mysql_query("SELECT * FROM products WHERE category = $mycateg LIMIT $offset, $rowsPerPage") or die('Error, query failedd');
You need to put $mycateg between stripes, or whatever they're called, like this: '$mycateg'
edit: strings needs to be between " " or ' ', integers (numbers) can be without them.

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 10:21 am
by simonmlewis
Yes, the error is this:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Airsoft Guns LIMIT -20, 20' at line 1

Re: Limiting results to first 20 results, then next 20, then...

Posted: Sun Jun 07, 2009 10:43 am
by Raph
It's time for me to go vote in the EU-election, so I'll make this quick.
Did you embed your $mycat with the stripes?

Code: Select all

$result = mysql_query("SELECT * FROM products WHERE category = '$mycateg' LIMIT $offset, $rowsPerPage") or die(mysql_error());
Does that output an error? Since I get an error when using WHERE class = $class, but the query works perfectly fine when using WHERE class = '$class'.

edit:
If it still won't work, use the query in phpMyAdmin.

Code: Select all

SELECT * FROM products WHERE category = CategoryName LIMIT 0, 20