MySQL next and previous record of the current record

I have thought a lot these days what I should write some tips for beginner. Now, I got it. Have you ever noticed when you visiting and reading on some blogs? You will see previous and next links of current post. I don’t mean pagination links. If you have seen , have you ever thought how to retrieve those data from MySQL ? OK , If you haven’t thought and tried yet. Let’s see how to do it.

Let says, we have user table. Normally, we write SQL query like :

SELECT * FROM user WHERE user_id = 111

OK, If your user_id is added by auto increment, it’s simply write 111 – 1 and 111 + 1 in your program. If not, how could we got it?
Let’s see.

SELECT *,
(SELECT user_id FROM user WHERE user_id < u.user_id ORDER BY user_id DESC LIMIT 1) AS user_prev,
(SELECT user_id FROM user WHERE user_id > u.user_id ORDER BY user_id ASC LIMIT 1) AS user_next
FROM user AS u WHERE u.user_id=111

Enjoy !!! I hope it would be useful for you.