Get next and previous record in MySQL?

how-to-get-next-and-previous-record-in-mysql

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

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

In this article we will see how to get next and previous record 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 the 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 next and previous records. And thus you can fetch the details of 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 statment. However, this is not needed to use LIMIT because we have used the MAX() and MIN() functions that returns a single record.

Related Posts