Page 1 of 3

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

Posted: Fri Apr 12, 2013 4:29 am
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?

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

Posted: Fri Apr 12, 2013 12:22 pm
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;
}

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

Posted: Fri Apr 12, 2013 12:28 pm
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.

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

Posted: Fri Apr 12, 2013 12:42 pm
by requinix
Right, I just meant that the end result is 56 on the first page and 44 on the second.

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

Posted: Fri Apr 12, 2013 2:56 pm
by simonmlewis
Sorry how I put that into my MySQL query? $rowCount. I don't know where that should go.

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

Posted: Fri Apr 12, 2013 3:09 pm
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?

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

Posted: Fri Apr 12, 2013 3:18 pm
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))

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

Posted: Fri Apr 12, 2013 3:29 pm
by pickle
Echo the query

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

Posted: Fri Apr 12, 2013 3:43 pm
by simonmlewis
How do I do that please - I always flippin forget.

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

Posted: Fri Apr 12, 2013 3:51 pm
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);

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

Posted: Fri Apr 12, 2013 4:00 pm
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.

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

Posted: Sat Apr 13, 2013 3:32 am
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

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

Posted: Sat Apr 13, 2013 5:34 pm
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.

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

Posted: Sun Apr 14, 2013 2:34 am
by simonmlewis
This one does! Plus, we sometime reset that to a higher number to move to top. That's just how it works .

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

Posted: Wed May 15, 2013 4:42 am
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?