Posted by Thurein on 7th April 2010
2

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.

  • About Thurein Soe
    Visit Thurein's website.

    Freelance Web Developer. Founder of The Tech Space. Follow me on Twitter.

      • 2 Responses

    1. Saw Htoo says:
      April 7, 2010 at 9:58 pm

      You can use the following code for wordpress. :) 

      < ?php
     previous_post(' %', 'Previous: ', 'yes');
     next_post(' %', 'Next: ', 'yes');
?>
    2. Helen Neely says:
      November 23, 2011 at 6:45 am

      Nice, but very short. Would be nice if you could spend more time on it.

    Post your comments