A common requirement for web applications is the ability to be able to move to the next and previous record when viewing a record, think of a leads system where a sales person would go through the leads one by one by clicking a next or previous button whilst viewing the record.
Making a previous and next query for MySQL is simple enough but if you want to go through the order alphabetically instead of id order it’s a little more tricky.
Using a UNION query means you can get both the previous and next record in one query. How this works is first select the columns from the table where the column is less that the current record, then order that by that column in descending order to get the last record.
For the next query it’s the same thing but don’t use descending in the order.
(SELECT id, title FROM companies WHERE title < 'Fictional Company' ORDER BY title desc LIMIT 1)
UNION
(SELECT id, title FROM companies WHERE title > 'Fictional Company' ORDER BY title LIMIT 1)
So this query will return 2 records as long as there is a next and previous record.
