Page 3 of 6

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

Posted: Sun Jun 07, 2009 12:54 pm
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?

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

Posted: Sun Jun 07, 2009 12:57 pm
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.

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

Posted: Sun Jun 07, 2009 1:02 pm
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.

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

Posted: Sun Jun 07, 2009 1:12 pm
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.

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

Posted: Sun Jun 07, 2009 1:20 pm
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);
?>

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

Posted: Sun Jun 07, 2009 1:25 pm
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.

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

Posted: Sun Jun 07, 2009 1:28 pm
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);
?>

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

Posted: Sun Jun 07, 2009 1:34 pm
by Raph
Is your table named table?

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

Posted: Sun Jun 07, 2009 1:38 pm
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!!!

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

Posted: Sun Jun 07, 2009 1:41 pm
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...?

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

Posted: Sun Jun 07, 2009 1:42 pm
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?

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

Posted: Sun Jun 07, 2009 1:52 pm
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);
?>

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

Posted: Sun Jun 07, 2009 2:27 pm
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>

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

Posted: Sun Jun 07, 2009 2:36 pm
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

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

Posted: Sun Jun 07, 2009 2:38 pm
by Raph
What does your query look like? Not in the script, the actual query that is sent to the DB.