selecting row before and after some ID

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Miha
Forum Newbie
Posts: 10
Joined: Mon Aug 21, 2006 12:44 pm

selecting row before and after some ID

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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
Miha
Forum Newbie
Posts: 10
Joined: Mon Aug 21, 2006 12:44 pm

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
Post Reply