Page 2 of 6

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

Posted: Sun Jun 07, 2009 10:57 am
by simonmlewis
Without with the '' it errors.

If without, the error is as before with the category name in the error report.

This is at the top. it's "category=Todays category".... and "Todays category" is what it must find in the table to get the appropriate products.

Code: Select all

$mycateg = $_REQUEST['category'];
My alternative is to do the Random thing, and just produce a text vertical list on the left of all products, but if the category has 500 products, it's one huge list!!

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

Posted: Sun Jun 07, 2009 11:18 am
by Raph
Can you print_r($mycat); before the mysql_query()? It might be as you say, that it doesn't like the space inbetween the two words in $mycat.

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

Posted: Sun Jun 07, 2009 11:26 am
by simonmlewis
Yes - it renders it in

Code: Select all

echo "<div class='categorytitle'>$mycateg</div>";
Accessories
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 '-20, 20' at line 1
... where "Accessores" is taken 'without a space' from the $mycateg.

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

Posted: Sun Jun 07, 2009 11:33 am
by Raph
The problem isn't the code, it's the output.

Code: Select all

echo $mycateg ."<br />";
echo "SELECT * FROM products WHERE category = '$mycateg' LIMIT $offset, $rowsPerPage";
Copy and paste the results into this thread, so we can troubleshoot it. It's hard to know where exactly the problem lies without knowing the exact output of the code.

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

Posted: Sun Jun 07, 2009 11:38 am
by simonmlewis
I don't see why you ECHOd the SELECT query, but I did implement your mycateg code.

This is the result.
Accessories
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 '-20, 20' at line 1

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

Posted: Sun Jun 07, 2009 11:39 am
by simonmlewis
And this is the entire code in the page.

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['page'] defined, use it as page number
if(isset($_GET['page']))
{
    $pageNum = $_GET['page'];
}
 
// 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?page=$page\">$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=\"$self?page=$page\">[Prev]</a> ";
 
   $first = " <a href=\"$self?page=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?page=$page\">[Next]</a> ";
 
   $last = " <a href=\"$self?page=$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 11:58 am
by Raph
I think we have abit of a language barrier at the moment.

The code is fine. It works 100%. I've tested it myself on my server. I've tried different values of $mycateg, ranging from integers, to single words and complete sentences. It still works flawlessly. The problem is not the code.

Somewhere in your script one ore more variables (the ones with a $-sign infront) is assigned a value that makes something brake. In order to know what variable has a faulty value is to echo / print / dump them, and verify the contents manually.

That is why I asked you to echo your query, and not run it through your database. We need to read it with our own eyes, manually, in order to make sure it checks out.

Code: Select all

SELECT * FROM products WHERE category = '$mycateg' LIMIT $offset, $rowsPerPage
Assuming $mycateg is a valid string, which you yet have confirmed, you still have two more variables which can contain all sorts of values. There's also inconsistencys in your code. You first direct your links to index.php (row 29), and later on in the script, you direct the links to PHP_SELF. Is the two the same file, or two different files?

What does $_REQUEST['category'] contain? print_r() or var_dump() it. Do the same with $mycateg, and please share the output with us. The output should not be a SQL-error, since echo, var_dump() and/or print_r() does not interact with the database.

My guess is that there's a problem with $_REQUEST['category'], which in turn causes $mycateg to inherit the error, which in turn causes a SQL-syntax error.

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

Posted: Sun Jun 07, 2009 12:09 pm
by simonmlewis
What does $_REQUEST['category'] contain? print_r() or var_dump() it. Do the same with $mycateg, and please share the output with us. The output should not be a SQL-error, since echo, var_dump() and/or print_r() does not interact with the database.
This contains whatever is passed from ....index.php?page=product&menu=categ&category=RC Boats...... where is would be "RC Boats".

I know this as $mycateg is doing exactly that - rendering it to the page where you read "Accessories".

While I could perhaps understand a problem with /&_.... for example in the content, 'Accessories' clearly contains none of this.

I am using PHP_SELF as that was your code, so used as provided.
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 '-20, 20' at line 1
I don't understand how $mycateg corresponds to this error though. It's simply searching for everything in the table, where the category is 'Accessories' (for example), and then the LIMIT part.

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

Posted: Sun Jun 07, 2009 12:18 pm
by Raph
Allright. Assuming your $mycateg is accurate, can you output your sql-syntax, like you did with $mycateg. Since you do get an SQL-error, there is something funky with it, we just need to figure out what.

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

Posted: Sun Jun 07, 2009 12:24 pm
by simonmlewis
Brain malfunction.... can I remind me how to output the query.... so we can see what's going on?

This is the result still, by the way:
RC Boats
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 'Boats LIMIT -20, 20' at line 1

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

Posted: Sun Jun 07, 2009 12:25 pm
by Raph
On the row above your mysql_query(), add this line:

Code: Select all

echo "SELECT * FROM products WHERE category = '$mycateg' LIMIT $offset, $rowsPerPage"; die;
It should output your query exactly like MySQL recieves it, and then aborts the remainder of the script, so you don't get the SQL-error.

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

Posted: Sun Jun 07, 2009 12:30 pm
by simonmlewis
Sorry, yes I see why you wanted to now.

This was the result:
RC Boats
SELECT * FROM products WHERE category = 'RC Boats' LIMIT -20, 20

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

Posted: Sun Jun 07, 2009 12:39 pm
by Raph
And now I wonder why I've been so stupid. You've virtually mashed my face with your error all along.

The problem is your LIMIT -20. It can't be a negative value. Up until now I just assumed the "-" sign came from the $mycateg.
The problem is the $offset variable. For some reason it dips below 0 on what I assume is the first page of the paging-system.

edit:
Do you count your first page as 0 by any chance?

(0 - 1) * 20 equals -20

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

Posted: Sun Jun 07, 2009 12:43 pm
by simonmlewis
Oh...?

Do you know what needs to be done for it to work?

Sorry for the mashing. :P

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

Posted: Sun Jun 07, 2009 12:48 pm
by Raph
Yeah, copy and paste the URL on the page where you get your SQL-error. I'd like to see if you define &page=x not.