Need to add a next previous single article navigator in php based CMS

Question

I've built a simple article website which lists articles of different categories. I've even implemented a simple pagination system whereinwhich visitors can browse through article lists. The client however has another requirement. They want that when reading an article[not on an article listing page] - there should be a 'next article' and 'previous article' link that links to the next/previous article in the article list.

Consider that the listing can be sorted differently so the next and previous links for reading an individual article should also reflect accordingly how can this be implemented. Its kinda like mini pagination I believe.

Is it possible to get in a single sql query the next and previous rows especially when selecting a single tuple?

Answer 1

Previous ID: 
SELECT id FROM $mytable WHERE id < $id ORDER BY ID DESC LIMIT 1;
Next ID: 
SELECT id FROM $mytable WHERE id > $id ORDER BY ID ASC LIMIT 1;

and after $id you should add your filters that you are using in your page :)

Answer 2

SELECT id FROM $mytable WHERE id < $id ORDER BY ID DESC LIMIT 1;
UNION 
SELECT id FROM $mytable WHERE id = $id ORDER BY ID ASC LIMIT 1;
UNION 
SELECT id FROM $mytable WHERE id > $id ORDER BY ID ASC LIMIT 1;

You will Get all three id's

Answer 3

If you are looking to execute everything in one SQL line while considering that the listing can be sorted differently, you would have to use a SESSION/COOKIE to store the SQL line on submit. Then you can append the criteria after WHERE to what @Eddsstudio suggested below:

SELECT id FROM $mytable WHERE id < $id ORDER BY ID DESC LIMIT 1;
Next ID: 
SELECT id FROM $mytable WHERE id > $id ORDER BY ID ASC LIMIT 1;