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.