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

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post by simonmlewis »

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;	

$result = mysql_query ("SELECT * FROM products WHERE pause = 'off' AND stock <> 'out of stock' ORDER BY id DESC LIMIT $offset, $rowsPerPage");
while ($row = mysql_fetch_object($result))
      {
This is my current script. It does this to enable page numbering further down. Each page can have 56 items on it.
I know LIMIT tells it how many to extract from the DB.

But here's the question: I want to also limit howo many "overall" it extracts.

So it shows 56 per page. 100 overall.

Any means of doing 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 »

So it should show slightly less than two pages?

Use another variable for the amount to find in the LIMIT that could be $rowsPerPage but caps at (however many it takes to only show up to the 100th row):

Code: Select all

if ($offset + $rowsPerPage > 100) {
    $rowCount = max(0, 100 - $offset);
} else {
    $rowCount = $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 »

It's not so much trying to be just under two pages. I want to say how many per page, but also how many 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 »

Right, I just meant that the end result is 56 on the first page and 44 on the second.
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 how I put that into my MySQL query? $rowCount. I don't know where that should go.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

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

Post by pickle »

Put $rowCount in your query where $rowsPerPage currently is. You're wanting to change the number of rows returned depending on the offset. $rowsPerPage doesn't change, but $rowCount will.

@requinix - why the call to max()? Why not just a simple assignment?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
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 now have this, but it still pulls up everything in the DB.

Code: Select all

// if $_GET['pagenum'] defined, use it as page number
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}

$rowsPerPage = 56;
// by default we show first page
$pageNum = 1;
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;	
	  // how many rows to show per page
 
if ($offset + $rowsPerPage > 100) {
    $rowCount = max(0, 100 - $offset);
} else {
    $rowCount = $rowsPerPage;
}

$result = mysql_query ("SELECT * FROM products WHERE pause = 'off' AND stock <> 'out of stock' ORDER BY id DESC LIMIT $offset, $rowCount");
while ($row = mysql_fetch_object($result))
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

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

Post by pickle »

Echo the query
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
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 »

How do I do that please - I always flippin forget.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

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

Post by pickle »

Really?

Code: Select all

$result = mysql_query ("SELECT * FROM products WHERE pause = 'off' AND stock <> 'out of stock' ORDER BY id DESC LIMIT $offset, $rowCount");
becomes

Code: Select all

$query = "SELECT * FROM products WHERE pause = 'off' AND stock <> 'out of stock' ORDER BY id DESC LIMIT $offset, $rowCount";
echo $query;
$result = mysql_query ($query);
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
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 »

pickle wrote:@requinix - why the call to max()? Why not just a simple assignment?
Making sure the $rowCount doesn't go below zero. Like if the page number was too high (because someone manipulated the URL) then the $offset would be >100 and the $rowCount would be negative. Assuming a negative number is a bad thing.
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;
// by default we show first page
$pageNum = 1;
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;	
	  // how many rows to show per page
 
if ($offset + $rowsPerPage > 100) {
    $rowCount = max(0, 100 - $offset);
} else {
    $rowCount = $rowsPerPage;
}

$query = "SELECT * FROM products WHERE pause = 'off' AND stock <> 'out of stock' ORDER BY id DESC LIMIT $offset, $rowCount";
echo $query;
$result = mysql_query ($query);
That produces:
SELECT * FROM products WHERE pause = 'off' AND stock <> 'out of stock' ORDER BY id DESC LIMIT 0, 56
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 »

Looks good to me.

If you're sorting by the ID because you want the most recent products first then you really should be sorting by an actual date field instead. The ID should always be treated like it was a mere (unique) number - don't give any additional meanings to it like "higher numbers mean more recent products", despite the fact that it may work like that.
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 »

This one does! Plus, we sometime reset that to a higher number to move to top. That's just how it works .
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
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 »

Hi guys
Still not there with this.
Need to LIMIT 0,36 per page, but also limit how many it extracts overall.

Surely this is simple to do?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply