Get two previous and next rows data from current id SQL PHP

Question

i have a table with database and i want get two data before current id and get two data after current id.

primary_key     id
1               345
2               356
3               400
4               102
5               210
6               190

Case:

• If current id defined 400, the results as before is 356 & 345 and after is 102 & 210
• If current id defined 210, the results as before is 102 & 400 and after is 190
• If current id defined 356, the results as before is 345 and after is 400 & 102
• If current id defined 345, the results as before is NULL and after is 356 & 400
• If current id defined 190, the results as before is 210 & 102 and after is NULL

I try this SQL but not working fine,

$define_id = 400;

SELECT *
FROM table_name

WHERE ( 
    id = IFNULL(
        (
            SELECT MIN(id)
            FROM table_name
            WHERE id > $define_id
        ), 0 )

    OR id = IFNULL(
        (
            SELECT MAX(id)
            FROM table_name
            WHERE id < $define_id
        ), 0 )
) 

LIMIT 2

The code success to get before and after data, but only one before and one after. I want get result two before and two after.

Please help.

Answer 1

Tried with the outputs you mentioned in the question. If this is not you want please explain more clearly.

    CREATE TABLE IF NOT EXISTS `docs` (
  `primary_key` int(6) unsigned NOT NULL,
  `id` int(3) unsigned NOT NULL,
  PRIMARY KEY (`primary_key`,`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`primary_key`, `id`) VALUES
  ('1', '345'),
  ('2', '356'),
  ('3', '400'),
  ('4', '102'),
  ('5', '210'),
  ('6', '190');

Query

(SELECT * FROM docs WHERE primary_key < (SELECT primary_key FROM docs WHERE id = 400) ORDER BY primary_key DESC LIMIT 2)
UNION ALL
(SELECT * FROM docs WHERE primary_key > (SELECT primary_key FROM docs WHERE id = 400) ORDER BY primary_key ASC LIMIT 2);

Sqlfiddle link http://sqlfiddle.com/#!9/e11c8d/2

Answer 2

One method uses union all for the two groups:

(select 'before' as which, t.*
 from t cross join
      (select primary_key from t where id = $define_id) x
 where t.primary_key < x.primary_key
 order by t.primary_key desc
 limit 2
) union all
(select 'after' as which, t.*
 from t cross join
      (select primary_key from t where id = $define_id) x
 where t.primary_key > x.primary_key
 order by t.primary_key asc
 limit 2
);

Answer 3

You can do it with min and max functions, So for next

SELECT * FROM `table` WHERE  id = (select min(id) from `table` where id > YOUR_ID)

And for previous:

SELECT * from `table` where id = (select max(id) from table where id < YOUR_ID)