Strange query results for dynamic navigation

After asking my last question I thought the problem was solved, and the answer provided was working when I tried it out.

The issue right now is the navigation selects the previous and next row in the database until the product_id is 100+ then it fails.

Here is the code I'm using to generate the links:

$prevProduct = $prod - 3;
$queryNextProductID = 'SELECT product_id FROM products WHERE product_id > '.$prod.' AND   category='.$cat.' LIMIT 1';
$queryPrevProductID = 'SELECT product_id FROM products WHERE product_id < '.$prod.' AND   category='.$cat.' LIMIT '.$prevProduct.',1';

The $queryPrevProductID returns the previous row in the table until 100+, when it returns an empty result set.

Heres a simplified version of my table to better explain the issue:

product_id
1
3
5
80
103
104

On the page of product_id=3 the Previous link will be 1 and the Next link will be 5

On the page of product_id=80 the Previous link will be 5 and 开发者_Python百科the Next link will be 103

On the page of product_id=103 the Previous link will not be there, and the Next link will be 104

Any ideas on the problem?

Change your query. Using the limit with offset is not needed. Also you need to use "ORDER BY product_id" or the next product and "ORDER BY product_id DESC" for the previous product.

$queryNextProductID = 'SELECT product_id FROM products WHERE product_id > '.$prod.' AND   category='.$cat.' ORDER BY product_id LIMIT 1';
$queryPrevProductID = 'SELECT product_id FROM products WHERE product_id < '.$prod.' AND   category='.$cat.' ORDER BY product_id DESC  LIMIT 1';

You are using the LIMIT like this:

LIMIT '.$prevProduct.',1'

That will ran into

LIMIT 100,1

Which doesn't limit your result set to 1 row after row where 'product_id' = 100, but 1 row after 100th row in result.

Just remove the code '.$prevProduct.' and use ORDER BY product_id to sort results.