Oct
30
In SQL, this is a common issue to query the previous and next entries of the database. In order to achieve this, we are going to use a table that is sorted by the `id` field. To get the previous, the technique consists in sorting all the fields that are lower that the current one and taking the first one.
SELECT * FROM `table` WHERE `id` < $current_id ORDER BY `id` DESC LIMIT 1 |
Let's take an example with the values [8, 3, 4, 9, 6, 1]. We want to get the previous of 8.
WHERE `id` < $current_id
We first take all the values strictly lower than8
Result:[3, 4, 1, 6]ORDER BY `id` DESC
We then sort them in descending order
Result:[6, 4, 3, 1]LIMIT 1
We take the first one
Result:6
You can apply the same method for the next entry:
SELECT * FROM `table` WHERE `id` > $current_id ORDER BY `id` ASC LIMIT 1 |
