Page 2 of 3

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Wed May 15, 2013 12:17 pm
by requinix
Adjust your logic so that it never generates a LIMIT that goes too far: the starting offset should never be too high (otherwise they're past the end of results) and offset+length should never be too high (otherwise you need to adjust the length accordingly).

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Wed May 15, 2013 12:26 pm
by simonmlewis
Huh?

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Wed May 15, 2013 12:37 pm
by requinix

Code: Select all

LIMIT 10000, 10
is bad because 10000 is too high. Don't let that happen.

Code: Select all

LIMIT 10, 10000
is bad because 10+10000 is too high. Don't let that happen either.

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Wed May 15, 2013 12:41 pm
by simonmlewis
So if I want to show 36 per page and 100 overall, how do I do that?

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Wed May 15, 2013 12:53 pm
by requinix
First page is 0,36 (stops at 36).
Second page is 36,36 (stops at 72).
Third page would have been 72,36 but that would stop at 108 so instead it's 72,(100-72=)28.

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Wed May 15, 2013 1:02 pm
by simonmlewis
But before all that ,you have this:

Code: Select all

	  // how many rows to show per page
$rowsPerPage = 56;
 
// by default we show first page
$pageNum = 1;
 
// if $_GET['pagenum'] defined, use it as page number
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;	
And then

Code: Select all

SELECT * FROM products  ORDER BY id DESC LIMIT $offset, $rowsPerPage
Surely the $rowperpage will screw up because that will "limit" how many rows per page.

Or do I need to alter Offset and Rowsperpage, in order to get x per page, and x overall?

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Wed May 15, 2013 1:55 pm
by requinix
simonmlewis wrote:Or do I need to alter Offset and Rowsperpage, in order to get x per page, and x overall?
Yes. After you've calculated the $offset you need to check if $offset + $rowsPerPage goes too far, and if so adjust the LIMIT accordingly. I suggest a second variable that goes into the query so you can leave $rowsPerPage alone.

1. Set page number with a default of 1
2. Calculate offset
3. Set the number of rows to retrieve with a default of $rowsPerPage
4. Adjust that number if it + $offset is too high. Like use (maximum results to show) - $offset
5. Stick that number in the LIMIT instead of $rowsPerPage

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Wed May 15, 2013 1:59 pm
by simonmlewis
I'm being thick.
I see LIMIT as fi example 0,36
How do write the variables in the code to make this work?

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Wed May 15, 2013 2:28 pm
by requinix
You already have code that's very close to the steps I just posted. All you need is to make a couple changes to get it to match up.

If you're not sure how to do it, what is your code now?

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Wed May 15, 2013 2:32 pm
by simonmlewis

Code: Select all

$rowsPerPage = 56;
$pageNum = 1;
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}
$offset = ($pageNum - 1) * $rowsPerPage;	

$result = mysql_query ("SELECT * FROM products WHERE pause = 'off' ORDER BY id DESC  LIMIT LIMIT $offset, $rowsPerPage");
To me, this will just show the lot, but at 56 per page until it's run out.
So if I want to show 150 products *in all*......?

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Wed May 15, 2013 4:27 pm
by requinix

Code: Select all

// 1. set page number with a default of 1
$rowsPerPage = 56;
$pageNum = 1;
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}

// 2. calculate offset
$offset = ($pageNum - 1) * $rowsPerPage;	

// 3. set the number of rows to retrieve with a default of $rowsPerPage
$rowsToRetrieve = /* ??? */;

// 4. adjust that number if it + $offset is too high. like use (maximum results to show) - $offset
if (/* number + $offset is too high */) {
    $rowsToRetrieve = /* (maximum results to show) - $offset */;
}

// 5. stick that number in the limit instead of $rowsPerPage
$result = mysql_query ("SELECT * FROM products WHERE pause = 'off' ORDER BY id DESC LIMIT $offset, ???");

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Wed May 15, 2013 4:35 pm
by simonmlewis
Sorry.... what have you put in ??? at the end? Should that be "LIMIT LIMIT $offset, $rowToRetrieve?

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Wed May 15, 2013 7:30 pm
by requinix
I'm trying really hard to get you to think about what's going on rather than just give you the code and get it all over with.
simonmlewis wrote:Should that be "LIMIT LIMIT $offset, $rowToRetrieve?
The extra LIMIT is a typo which I've now fixed, but the rest sounds right. Does it work?

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Thu May 16, 2013 2:14 am
by simonmlewis
That products results, but the next stage is the page numbering at the foot of the page.
This shows 20 pages. With 150 requested, and 56 per page, it shouldn't be 20 pages...... am I missing something?

Code: Select all

$query   = "SELECT COUNT(id) AS numrows FROM products WHERE pause = 'off'";

 $categ = "$cname"; 
 $findcateg ="/ /"; 
 $replacecateg ="-"; 
 $categreplace = preg_replace ($findcateg, $replacecateg, $categ); 

$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=\"/categ/page/$catid/$categreplace/$page\" class='bodylink'>$page</a>";
   }
}
 
if ($pageNum > 1)
{
   $page  = $pageNum - 1;
   $prev  = " <a href=\"/categ/page/$catid/$categreplace/$page\" class='bodylink'>[Prev]</a> ";
 
   $first = " <a href=\"/categ/$catid/$categreplace/\" class='bodylink'>[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=\"/categ/page/$catid/$categreplace/$page/\" class='bodylink'>[Next]</a>";
 
   $last = " <a href=\"/categ/page/$catid/$categreplace/$maxPage\" class='bodylink'>[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 "<div class='navpages'>" . $first . $prev . $nav . $next . $last . "</div>";

Re: How do I limit amount of items found, AS WELL AS per pag

Posted: Thu May 16, 2013 2:31 am
by requinix

Code: Select all

SELECT COUNT(id) AS numrows FROM products WHERE pause = 'off'
Because you're using the total count to calculate the number of pages. If you don't want to show more than whatever-the-number-is-today then make sure you don't go above that.