How do I limit amount of items found, AS WELL AS per page?
Moderator: General Moderators
Re: How do I limit amount of items found, AS WELL AS per pag
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
Huh?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: How do I limit amount of items found, AS WELL AS per pag
Code: Select all
LIMIT 10000, 10Code: Select all
LIMIT 10, 10000-
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
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.
All the best from the United Kingdom.
Re: How do I limit amount of items found, AS WELL AS per pag
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.
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
But before all that ,you have this:
And then
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?
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; Code: Select all
SELECT * FROM products ORDER BY id DESC LIMIT $offset, $rowsPerPageOr 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.
All the best from the United Kingdom.
Re: How do I limit amount of items found, AS WELL AS per pag
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.simonmlewis wrote:Or do I need to alter Offset and Rowsperpage, in order to get x per page, and x overall?
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
I'm being thick.
I see LIMIT as fi example 0,36
How do write the variables in the code to make this work?
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.
All the best from the United Kingdom.
Re: How do I limit amount of items found, AS WELL AS per pag
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?
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
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");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.
All the best from the United Kingdom.
Re: How do I limit amount of items found, AS WELL AS per pag
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
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
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.
All the best from the United Kingdom.
Re: How do I limit amount of items found, AS WELL AS per pag
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.
The extra LIMIT is a typo which I've now fixed, but the rest sounds right. Does it work?simonmlewis wrote:Should that be "LIMIT LIMIT $offset, $rowToRetrieve?
-
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
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?
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 = ' '; // we're on page one, don't print previous link
$first = ' '; // 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 = ' '; // we're on the last page, don't print next link
$last = ' '; // 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.
All the best from the United Kingdom.
Re: How do I limit amount of items found, AS WELL AS per pag
Code: Select all
SELECT COUNT(id) AS numrows FROM products WHERE pause = 'off'