Task is to show previous and next link to smth. For example next\previous product in category.
It is hard to create this links because ids are not in a sequance (some entries were deleted or there is an order).
And, naturally, we dont want to select all ids and search in loop :)
Ноwto do it in one sql-query
Lets have in our table `main_table` fields `id` and `name`. Last one in order to sort.
CREATE TABLE `main_table`(
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NULL,
PRIMARY KEY (`id`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT;
REPLACE INTO
main_table (id, name)
VALUES
(1, '1st entry'),
(2, '2nd entry'),
(3, '3rd entry'),
(5, '4th entry, but id is 5');
Getting prev ID.
SELECT
t1.id, t1.name, t1.prev as prev_id
FROM
(SELECT mt.id, mt.name, @prev_id as prev, @prev_id := id
FROM `main_table` mt, (SELECT @prev_id:=0) ni
ORDER BY name ASC) as t1;
Here is hack to declare variable
@prev_id variable which contains:
(SELECT @prev_id:=0) pi
And:
@prev_id as prev, @prev_id := id
Mysql place @prev_id to column 'prev' of result set, and than set @prev_id to current id, which on the next iteration will be previous. For the first entry @prev_id will be 0.
Getting next ID.
Basically, to get next ID we need just change ordering. In our example from ASC to DESC
SELECT
t1.id, t1.name, t1.next as next_id
FROM
(SELECT mt.id, mt.name, @next_id as next, @next_id := id
FROM `main_table` mt, (SELECT @next_id:=0) ni
ORDER BY name DESC) as t1;
And finally:
SELECT
t1.next as next_id
FROM
(SELECT mt.id, mt.name, @next_id as next, @next_id := id
FROM `main_table` mt, (SELECT @next_id:=0) ni
ORDER BY name DESC) as t1
WHERE t1.id = 3;
