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?
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.