Page 1 of 1

selecting row before and after some ID

Posted: Mon Sep 18, 2006 11:20 am
by Miha
Hi!

I would like to select a row before and after some specific ID(integer). IDs are integers - auto incremented, but it's possible, that there is a gap between two IDs....for example i have rows with IDs 1,2,3,6,7,8,15,17. How can i select a row with next higher ID if there is one, and how can i select a row with next lower ID if there is one?

regards, Miha.

Posted: Mon Sep 18, 2006 11:23 am
by GM
Why would you want to do this? I'm struggling to think of a reason. If you find that you have to do something like this, then it usually points to some problem in your database design.

EDIT: Sorry - I may have misunderstood your post, so I'll provide a solution...

Code: Select all

SELECT * FROM table_name WHERE id < ***id_to_find*** ORDER BY id DESC LIMIT 1
and

Code: Select all

SELECT * FROM table_name WHERE id > ***id_to_find*** LIMIT 1

Posted: Mon Sep 18, 2006 12:37 pm
by Miha
Just what i needed!

Code: Select all

SELECT * FROM slike WHERE id > $idPic ORDER BY id ASC LIMIT 1
and

Code: Select all

SELECT * FROM slike WHERE id < $idPic ORDER BY id DESC LIMIT 1
solved my problem.

Why do I need this? I made a photo gallery, and i have informations about photos stored in a table. With this, i can select a photo before and after a certain photo, so i can make buttons for 'next' and 'previous' photo. And there are gaps because there is an option to delete photos.

Problem in my database design...very much possible since i only have poor basic knowlage about DBs. But I'm still learning... :)


regards, Miha.

Posted: Tue Sep 19, 2006 2:57 am
by GM
No no - apologies for my initial post. I misread your question and made some wrong assumptions. I must learn not to criticise before I know the facts.

Glad the solution helped.