Get next and previous records in MySQL?

Let’s suppose we want to implement previous/next navigation on our page by using the ID of the current article. In this article, we will see how to get the next and previous records in MySQL database.

There are many applications where we need to get the next and previous records in MySQL. For example, shopping stores list products, and blogs list the articles.

Let’s suppose you are building a blog and want to get the next and previous navigation buttons on the article. We can achieve this using SQL queries. If the ID of the current article is 5, the previous navigation will be 4. For the next article, it will be 6 (if the IDs are in this order).

In this article, we will see how to get the next and previous records in MySQL.

The query to fetch the IDs are:

Get the next record in MySQL

SELECT * 
FROM blogs 
WHERE id = (
  SELECT MIN(id) 
  FROM blogs 
  WHERE id > 5
)

Get the previous record in MySQL

SELECT * 
FROM blogs 
WHERE id = (
  SELECT MAX(id) 
  FROM blogs 
  WHERE id < 5
)

It is possible that ID 4 does not exist in the database. The query is intelligent enough that it will handle the sequence of IDs itself.

In this way, you can get the IDs of the next and previous records. And thus you can fetch the details of the entire record using the SQL select queries.

SELECT * FROM blogs WHERE id = 6

This technique is very useful if you want to display the next and previous navigation buttons on a page.

SQL Limit statement

If you are getting multiple next and previous records, just simply use the LIMIT statement. However, this is not needed to use LIMIT because we have used the MAX() and MIN() functions that return a single record.