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 »

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!!
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 »

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.
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 »

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.
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 »

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.
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 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
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 »

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);
?>
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 »

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.
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 »

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.
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. 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.
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 »

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
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 »

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.
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, yes I see why you wanted to now.

This was the result:
RC Boats
SELECT * FROM products WHERE category = 'RC Boats' LIMIT -20, 20
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 »

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
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 »

Oh...?

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

Sorry for the mashing. :P
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 »

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.
Post Reply