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:

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

Post by simonmlewis »

Code: Select all

$query   = "SELECT COUNT(id) AS numrows FROM products WHERE pause = 'off' LIMIT $offset, $rowtoRetrieve";
This? As this errors.
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 »

What's the name of the variable? Does it match up with the one you put in the SQL?
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 do you mean?
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 »

The new variable I was talking about. You assigned it a value, right? What is the exact name of that variable?
Now look at your query. Look at the place where you put that variable in. What is its name? Is it exactly the same as the one before?
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 »

$rowToRetrieve ?
Yes.
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 »

If that "yes" means "yes they are the same variable" then no they are not. You gave a value to $rowToRetrieve but your query has $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

Post by simonmlewis »

Here's the lot. And it's still pulling up 30+ pages.

Code: Select all

<div class='head'>new products</div>
<?php 
$cookietype = $_COOKIE['type'];
include "dbconn.php";


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

$result = mysql_query ("SELECT * FROM products WHERE pause = 'off' ORDER BY id DESC LIMIT $offset, $rowToRetrieve");

while ($row = mysql_fetch_object($result))
      {
 $title = "$row->title"; 
 $findtitle ="/ /"; 
 $replacetitle ="-"; 
 $titlereplace = preg_replace ($findtitle, $replacetitle, $title); 
 
 $categ = "$row->catname"; 
 $findcateg ="/ /"; 
 $replacecateg ="-"; 
 $categreplace = preg_replace ($findcateg, $replacecateg, $categ); 

 $subcateg = "$row->subname"; 
 $findsubcateg ="/ /"; 
 $replacesubcateg ="-"; 
 $subcategreplace = preg_replace ($findsubcateg, $replacesubcateg, $subcateg); 
 
      $count = $count + 1;
      if ($count == 4 || $count == 8 || $count == 12 || $count == 16 || $count == 20 || $count == 24 || $count == 28 || $count == 32 || $count == 36 || $count == 40 || $count == 44 || $count == 48 || $count == 52 || $count == 56 || $count == 60)
      { echo "<div class='cat_prodlistbox_noline'>";}
      else { echo "<div class='cat_prodlistbox'>";}
      echo "<a href='/product/$row->catid/$categreplace/$row->subid/$subcategreplace/$row->id/$titlereplace' style='text-decoration: none'>
<div class='cat_producttitle'>$row->title<br/>Only ";
printf ("£%.2f", $row->price);
echo "</div>";
	if ($row->photoprimary == NULL) { echo "<img src='images/blank.gif' border='0'  style='border: 1px solid #000000' width='142px'/><img src='images/imageshadow.png' border='0'  class='shadowmedium' />";}
	if ($row->photoprimary <> NULL) { echo "<img src='images/productphotos/small/$row->photoprimary' border='0'  width='142px' style='border: 1px solid #000000'/><img src='images/imageshadow.png'  class='shadowmedium' border='0'/>";}
		echo "</a>
			</div>";
			if ($count == 4 || $count == 8 || $count == 12 || $count == 16 || $count == 20 || $count == 24 || $count == 28 || $count == 32 || $count == 36 || $count == 40 || $count == 44 || $count == 48 || $count == 52 || $count == 56 || $count == 60) 
      {
      if (strpos($_SERVER['HTTP_USER_AGENT'],'Firefox')!==FALSE) {
echo "<div style='clear: both;' /><br/><img src='images/divider.png' /></div>";}
      elseif (strpos($_SERVER['HTTP_USER_AGENT'],'Safari')!==FALSE) {
echo "<div style='clear: both;' /><br/><img src='images/divider.png' /></div>";}
elseif (strpos($_SERVER['HTTP_USER_AGENT'],'MSIE')!==FALSE) {
echo "<div style='clear: both;' /><br/><img src='images/divider_ie.png' /></div>";}
else { echo "<div style='clear: both;' /><br/><img src='images/divider_nogap.png' /></div>";}
}		
			}
	mysql_free_result($result);
	
	echo "<br/><br/><br/>";
		
	$query   = "SELECT COUNT(id) AS numrows FROM products WHERE pause = 'off' LIMIT $offset, $rowToRetrieve";

 $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>";
   }
}
 
// creating previous and next link
// plus the link to go straight to
// the first and last page
 
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
}
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.
Post Reply