MySQL Next/Previous Record

Question

What's the best solution to do this:

I've got a query (e.g. SELECT * FROM products WHERE category = 12 ORDER BY price DESC) and the user clicked on one of the products returned by this query.

So in this example the query returns the products 1,3,7,4,10,6,8,9 and 15. The user clicks on product 10. Now I want an "next" and a "previous" link to product 6 and to product 4.

How can I get those two ID's ?

Answer 1

First you should note that your ordering is not well-defined.

If two objects have the same price the relative order of these two items is indeterminate and could change from query to query. So your order by needs to have a tie-breaker to guarantee that the order will be consistent. If you have an id field you can use that as a tie-breaker:

ORDER BY price DESC, id

To implement next:

SELECT *
FROM products
WHERE category = 12
AND price = (SELECT price FROM products WHERE id = 10) AND id > 10
OR price > (SELECT price FROM products WHERE id = 10)
ORDER BY price DESC, id
LIMIT 1

Previous is similar but with the condition and order reversed:

SELECT *
FROM products
WHERE category = 12
AND price = (SELECT price FROM products WHERE id = 10) AND id < 10
OR price < (SELECT price FROM products WHERE id = 10)
ORDER BY price, id DESC
LIMIT 1

Answer 2

You could apply the same principal as suggested by Gokul N K except include the full order and add a start to the limit:

SELECT * FROM products WHERE category = 12 ORDER BY price, id DESC LIMIT 5, 5

(In your example product 10 was 5th in the list). Linking to the next and previous product would use exactly the same query just incrementing or decrementing the LIMIT restriction.

This way you only have to keep track of is the total number of items in the list and which one you are currently viewing.

Note that LIMIT M,N does not have great performance when M gets large.