David Carr

MySQL next record order by title

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)
(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.

Domains are often purchased from multiple providers, keeping track of where a domain is and its DNS settings can be tricky. Domain Mapper solves this by listing all your domains in one place. View your DNS settings and receive reminders to renew your domains. Try it today.

Support my work by donating with PayPal.

Subscribe to my newsletter

Subscribe and get my books and product announcements.

© 2009 - 2021 DC Blog. All code MIT license. All rights reserved.