tags:

views:

85

answers:

4

I have a table in database that is having unique id's. I have a page where one particular id is shown and link is given for next and previous id's. I have to get those prev and next id's by one single mysql query .All i have is that particular id on whose data we are currently working on. id's are not consecutive numbers as sometimes some id's get deleted also.(for eg. 1,2,3,7,9,10,15 etc)

So how can i get these two previous and next id's in one single query????

+4  A: 
SELECT
   (SELECT id FROM YourTable WHERE id < YourId ORDER BY id DESC LIMIT 1) AS prev,
   (SELECT id FROM YourTable WHERE id > YourId ORDER BY id ASC LIMIT 1) AS next
;

Replace YourId with the actual id and replace YourTable with valid table name. If any of the values will be NULL then there is no next/prev element to the given.

RaYell
its giving this error #1241 - Operand should contain 1 column(s)
developer
Make sure you have replaced all YourTable and YourId in the query. It definitely works, I checked it before posting.
RaYell
A: 

May be you try something like -

SELECT id FROM yourtable where id > '$current_page_id' LIMIT 1 for next

SELECT id FROM yourtable where id < '$current_page_id' LIMIT 1 for previous

you can do it one query as per @RaYell answer.

Wbdvlpr
A: 
SELECT MAX(id) FROM mytable WHERE id < 11
  • To get the id BEFORE 11

    SELECT MIN(id) FROM mytable WHERE id > 11

  • To get the id AFTER 11

Adrián
This is not in one query, is it?
RaYell
you can stick them together in just one query, not a problem. people also did while i was posting :)
Adrián
+1  A: 
SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        WHERE   id >= @current
        ORDER BY
                id
        LIMIT 2
        ) q1
UNION ALL
SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        WHERE   id < @current
        ORDER BY
                id DESC
        LIMIT 1
        ) q2
ORDER BY
        id
Quassnoi