How do I limit amount of items found, AS WELL AS per page?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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).
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

Huh?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

So if I want to show 36 per page and 100 overall, how do I do that?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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*......?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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, ???");
Last edited by requinix on Wed May 15, 2013 7:29 pm, edited 1 time in total.
Reason: put a limit on the limits
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

Sorry.... what have you put in ??? at the end? Should that be "LIMIT LIMIT $offset, $rowToRetrieve?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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>";
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

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

Post 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.
Post Reply