Previous Post: Cheat Sheet Index
Next Post: Emacs editor cheat sheets

How to select previous/next rows in MySql

Posted By Scott Klarr on Feb 06, 2008 at 10:16 pm

I decided to add the previous post & next post navigational links that you see at the top of this page for more internal cross-links as well as an easier means to browse posts. I went to type up the code really quick and had a temporary "coding block." So naturally I check google for a quick answer and found a few results of people asking for the same thing but no posted solution that I could see.

After thinking about it, i came up with 2 solutions that aren't very good, and then finally the "correct" answer - which of course ended up being extremely simple. I will first go over the two methods that are NOT the most ideal way to do it. Hopefully this will help somebody looking for the same solution.

Bad Idea #1

My first thought was to simply run a query like this:

SELECT id, title FROM topics WHERE id=($currentTopicId + 1) LIMIT 1

The problem is that in most real-world databases, including my blog, information isn't permanent. Sometimes things need to be deleted/edited/hidden/etc for whatever reason. Lets say the current topic's ID is 37 and you want to display a link to the following post, however there is no longer a topic with ID 38 because you deleted it. This would cause the above query to return nothing. The solution, if you can call it that, to this dilemma would be to setup a loop or recursive function to keep querying incremental IDs until a result is found. That, however, is a bad idea because it is a waste of resources to continually query the database like that when there is a better way to do it, which will be explained in a minute.

Bad Idea #2

The second idea was to pull a list of all the IDs from the database, use a loop to figure out the ID numbers before and after the current ID, and then pull those two rows from the database. Just as above, this would work ok, but wastes resources that become very valuable in larger scale applications.

The Best Way

Once this solution came to me I was kind of embarrassed that I actually had to think about it. Here is the MySQL query to get the next row:

SELECT id, title FROM topics WHERE id > $currentTopicId ORDER BY id ASC LIMIT 1

And for the previous record

SELECT id, title FROM topics WHERE id < $currentTopicId ORDER BY id DESC LIMIT 1

For a quick explanation of the first query: we are telling mysql to look for any rows in which the ID is larger than the current ID. It takes those results and orders them by the id in an ascending order (smallest to largest) and finally limits the result to the first of the list. So regardless of the difference in ID numbers, these two queries will allow you to get the next or previous available row. This of course is dependent on your table having auto-incremented IDs, which it probably should.

You can also add other statements to the where clause, for example this is the actual query I used in order to only count the topics I have set to visible:

SELECT id, title FROM topics WHERE id > $currentTopicId and visible=1 ORDER BY id ASC LIMIT 1

Related Links

Comments (2)

Post Comment

Your Name
Email Address
Website URL
Comment

Your email address will not be visible to the public