Limiting results to first 20 results, then next 20, then...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

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

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

I can tell you now, that I don't.

For the RC Boats one, the url is:
index.php?page=categ&menu=categ&category=RC%20Boats

Do I need to put "...&page=1" on the end as well?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

Code: Select all

index.php?[color=#FF0000]page=categ[/color]&menu=categ&category=RC%20Boats
There is your problem.
You can either decide to skip it from the URL, or change the script to look for another $_GET-value, and the problem should be solved.

edit:
You can try this though, it should work. Where you have this:

Code: Select all

if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
[color=#FF0000]if (intval($pageNum < 1)) $pageNum = 1;[/color]
}
Add the red text. Though I really don't know what you use the current $_GET['page'] for, it might screw something up that you're not showing us.
Last edited by Raph on Sun Jun 07, 2009 1:04 pm, edited 1 time in total.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

Sorry? i'm not with you.

Why have you highlighted categ?
categ.inc is the file where this script of yours runs.

Category at the end of that url is what the REQUEST picks up for $mycateg.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

Allright. The problem is that you use $_GET['page'] for one thing, and then the paging-script looks for that exact variable, and thinks its for the paging-system. It can easily be remedied by chaning the $_GET['page'] in the paging-script.

Code: Select all

<?php
[...]
 
// if $_GET['pagenum'] defined, use it as page number
if(isset($_GET['pagenum']))
{
    $pageNum = $_GET['pagenum'];
}
[...]
?>
And change all links at the bottom from ?page=x to ?pagenum=x.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

Now getting this
Accessories
SELECT * FROM products WHERE category = 'Accessories' LIMIT 0, 20
with this code

Code: Select all

<?php
$mycateg = $_REQUEST['category'];
echo $mycateg ."<br/>";
include "dbconn.php";
 
    // how many rows to show per page
$rowsPerPage = 20;
 
// 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;
 
 echo "SELECT * FROM products WHERE category = '$mycateg' LIMIT $offset, $rowsPerPage"; die;
$result = mysql_query("SELECT * FROM trimex_products WHERE category = $mycateg LIMIT $offset, $rowsPerPage") or die (mysql_error());
 
while ($row = mysql_fetch_array($result))
      {
            
            echo "
            <div class='cat_prodlistbox'>
            <div class='cat_producttitle'>
            ". $row['title'] ."</div>
            <a href='index.php?page=product&menu=categ&category=$mycateg&product=". $row['id'] ." title='Look at the ". $row['title'] ."><img src='images/productphotos/". $row['photoprimary'] ." border='0' /></a><br/>£". $row['price'] ."</div>
            ";
            }
 
    $query   = "SELECT COUNT(id) AS numrows FROM table";
$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=\"$self?pagenum=$page\">$pagenum</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=\"$self?pagenum=$page\">[Prev]</a> ";
 
   $first = " <a href=\"$self?pagenum=1\">[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=\"$self?pagenum=$page\">[Next]</a> ";
 
   $last = " <a href=\"$self?pagenum=$maxPage\">[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 $first . $prev . $nav . $next . $last;
    mysql_close($sqlconn);
?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

That's good. It means the SQL-query finally looks like it should. Just remove the entire line 21, and it all should work perfectly.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

Well... it does render the text results, but not the images.

Below the results I get this:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table' at line 1
.... and no page numbers.

Here's the code that's used:

Code: Select all

<?php
$mycateg = $_REQUEST['category'];
echo $mycateg ."<br/>";
include "dbconn.php";
 
    // how many rows to show per page
$rowsPerPage = 20;
 
// 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 category = '$mycateg' LIMIT $offset, $rowsPerPage") or die (mysql_error());
 
while ($row = mysql_fetch_array($result))
      {
            
            echo "
            <div class='cat_prodlistbox'>
            <div class='cat_producttitle'>
            ". $row['title'] ."</div>
            <a href='index.php?page=product&menu=categ&category=$mycateg&product=". $row['id'] ." title='Look at the ". $row['title'] ."><img src='images/productphotos/". $row['photoprimary'] ." border='0' /></a><br/>£". $row['price'] ."</div>
            ";
            }
 
    $query   = "SELECT COUNT(id) AS numrows FROM table";
$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=\"$self?pagenum=$page\">$pagenum</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=\"$self?pagenum=$page\">[Prev]</a> ";
 
   $first = " <a href=\"$self?pagenum=1\">[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=\"$self?pagenum=$page\">[Next]</a> ";
 
   $last = " <a href=\"$self?pagenum=$maxPage\">[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 $first . $prev . $nav . $next . $last;
    mysql_close($sqlconn);
?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

Is your table named table?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

By golly is works.

though I did have to alter the "next" "prev..." code to be this:

$next = " <a href=\"index.php?page=categ&menu=categ&category=$mycateg&pagenum=$page\">[Next]</a> ";

But that works.

Just got to try and implement that in the old code now.

WOW!!!
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: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

Actually.... why isn't it showing all the page numbers available, so someone can see if there are 4 pages, 25 pages etc...?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

That is an excellent question, since it displayed the amount of pages perfectly on my server. How many pages does it show, and how many results in total do you have in your DB?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

I have now added the other code in and it's producing nothing once again. Am trying this before I go thru the page number thing.

Code: Select all

<?php 
$mycateg = $_REQUEST['category'];
echo "<div class='categorytitle'>$mycateg</div>";
include "dbconn.php";
 
$result = mysql_query ("SELECT * FROM products WHERE cat = 'yes' AND category = '$mycateg'");
 
while ($row = mysql_fetch_object($result))
      {
            echo "
            <div class='categorypromotionbox'>
            <div class='producttitle'>$row->title";
 
 
$cookieuser = $_COOKIE['user'];
if (isset($_COOKIE['user'])) { echo "
<form method='post' action='index.php?page=a_productedit&menu=a_productshelp'>
<input type='hidden' name='id' value='$row->id'>
<input type='submit' value='Edit' class='submittext'>
</form>";
} 
 
echo "</div>
            <div class='productimage'>
            <a href='index.php?page=product&menu=categ&category=$row->category&product=$row->id'><img src='images/productphotos/$row->photoprimary' border='0' title='$row->title' ></a></div>
            <div class='productdescription'>";
            
            $position=320; //Defines how many characters will be displayed from content field.
$postcontent = substr($row->description,0,$position);
echo "$postcontent...<i>continued</i>...</div>
            <div class='productbuynow'><font color='#ff0000'>ONLY £$row->price</font>&nbsp;&nbsp;|&nbsp;&nbsp;";
            
            if ($row->stockstatus == "in stock") { echo "$row->cartbutton"; }
            elseif ($row->stockstatus == "sold out") { echo "Sorry, sold out."; }
            
            echo "</font></div>
            ";
            }
    mysql_free_result($result);
    echo "</div><img src='images/header_longblank.gif' style='margin-top:10px'/>";
    
$result = mysql_query ("SELECT * FROM products WHERE category = '$mycateg' AND stockstatus= 'in stock' ORDER BY title LIMIT $offset, $rowsPerPage") or die (mysql_error());
 
while ($row = mysql_fetch_object($result))
      {
            echo "
            <div class='cat_prodlistbox'>
            <div class='cat_producttitle'>";
            
                        $cookieuser = $_COOKIE['user'];
if (isset($_COOKIE['user'])) { echo "<div style='width:20px; float:right'>
<form method='post' action='index.php?page=a_productedit&menu=a_productshelp'>
<input type='hidden' name='id' value='$row->id'>
<input type='submit' value='Edit' class='submittext'>
</form></div>";
}
            
            echo ". $row['title'] . "</div>
            <a href='index.php?page=product&menu=categ&category=$mycateg&product=". $row['id'] . " title='Look at the ". $row['title'] ."><img src='images/productphotos/" . $row['photoprimary'] ."' border='0' /></a><br/>";
        
            
            echo "£". $row['price'] ."</div>";
            }
    
    $query   = "SELECT COUNT(id) AS numrows FROM trimex_products";
$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=\"index.php?page=categ&menu=categ&category=$mycateg?pagenum=$page\">$pagenum</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=\"index.php?page=categ&menu=categ&category=$mycateg&pagenum=$page\">[Prev]</a> ";
 
   $first = " <a href=\"index.php?page=categ&menu=categ&category=$mycateg&pagenum=1\">[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=\"index.php?page=categ&menu=categ&category=$mycateg&pagenum=$page\">[Next]</a> ";
 
   $last = " <a href=\"index.php?page=categ&menu=categ&category=$mycateg&pagenum=$maxPage\">[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 $first . $prev . $nav . $next . $last;
    mysql_close($sqlconn);
?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

You really must get a php-editor that has a syntax-check. You have a syntax-error on row 58:

Code: Select all

           echo ". $row['title'] . "</div>
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Limiting results to first 20 results, then next 20, then...

Post by simonmlewis »

Can u recommend one?

Even with that error fixed, I get this after the first query works:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Raph
Forum Commoner
Posts: 43
Joined: Wed May 27, 2009 6:33 pm

Re: Limiting results to first 20 results, then next 20, then...

Post by Raph »

What does your query look like? Not in the script, the actual query that is sent to the DB.
Post Reply