Hi, my name is David Carr and I'm a PHP Developer.


Blog Archives Categories Authors About Me Open Source Books Contact Me

Sponsor Me

Help support the blog so that I can continue creating new content!

My Latest Book

Laravel: The Modular way

Learn how to build modular applications with Laravel Find out more

MySQL next record order by title

PHP & MySQL

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

Copyright © 2009 - 2022 David Carr. All code MIT license. All rights reserved.