Dynamic navigation based on rows

Question

I'm creating a product rating system, to view a specific product, the following URL is used: www.example.com/category?product_id=1. The navigation on each page is simply the current product_id + 1 / -1, so if you were to view the example URL I just gave, back would go to product_id=0 and next would go to product_id=2. This works fine, until I delete a product from the database, and that product_id no longer exists, the URL it was at just displays a blank page.

What would be the correct way to turn this navigation into a dynamic one, so that it goes to the next product_id rather than a set product_id?

Answer 1

in pseudocode:

next_product_id = query_value("SELECT id FROM product WHERE id > $this_product_id ORDER BY id ASC LIMIT 1")

previous_product_id = query_value("SELECT id FROM product WHERE id < $this_product_id ORDER BY id DESC LIMIT 1")