MySQL - get previous and next rows in one query

Over on the Cortex blog we have a janky-ass blog system that uses some filthy PHP and MySQL to do news article type things. I wrote it, it’s disgusting, we should do better, but it is what it is.

The plumber’s toilet, and all that.

Pending some time to actually do it properly, we wanted to polish the turd enough that it was semi-functional - and this included having in-post navigation - so that, from a post you could either jump forward or backwards to the post. Much like on this blog. Which Hugo somehow just does automagically.

I won’t go in to the underlying structure extensively, but you cannot rely on e.g., the next and previous IDs of each post being the correct thing to navigate to. In fact, you need to navigate to the correct post in chronological (based on post-date) order (NOT ID order) but also check for things like is it still published (not been deleted) and assorted other things. This would be a trivial job to do IF - and that’s the kicker - IF you were happy issuing database calls for the post content, the previous link and the next link… i.e. at least 3 separate DB calls to get this stuff. That seems over the top.