MySQL

How to get next and previous record in MySQL?

0
76
2020 / 11 / 29

Lets suppose we are building a blog and wants to implement and get next and previous navigation buttons on the article. Lets suppose that the ID of current article is 5 and for previous navigation it will be 4 and for next article it will be 6 (if the IDs are in this order).

The query to fetch the IDs are:

Get Next

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

 

Get Previous

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

 

There may be a case that you don’t have the ID 4 exists in the database, but do not worry about it as the query will fetch the available existing record.

Hi, I am a full-stack web developer with 5+ years of experience in working with different web technologies. Do you want to ask something? just send me a message through the contact form. Please visit my portfolio at hamzamehmood.com. Thanks