MySQL next/previous ids (arbitrary sort order)

Question

Here is my table:

mysql> describe ps;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment | 
| a     | text    | YES  |     | NULL    |                | 
| b     | text    | YES  |     | NULL    |                | 
| c     | text    | YES  |     | NULL    |                | 
+-------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Let's say I have data as follows:

mysql> select * from ps;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 | x    | x    | x    | 
|  2 | x    | y    | x    | 
|  3 | x    | z    | x    | 
|  4 | x    | x    | y    | 
|  5 | x    | x    | z    | 
|  6 | x    | y    | x    | 
|  7 | x    | y    | y    | 
|  8 | x    | y    | z    | 
|  9 | y    | x    | x    | 
| 10 | z    | x    | x    | 
| 11 | y    | y    | x    | 
| 12 | y    | z    | x    | 
| 13 | y    | x    | y    | 
| 14 | y    | x    | z    | 
| 15 | z    | x    | x    | 
| 16 | z    | y    | x    | 
| 17 | z    | z    | x    | 
| 18 | z    | x    | y    | 
| 19 | z    | x    | z    | 
+----+------+------+------+
19 rows in set (0.00 sec)

My query is:

mysql> select * from ps where b = 'x' order by c;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 | x    | x    | x    | 
|  9 | y    | x    | x    | 
| 10 | z    | x    | x    | 
| 15 | z    | x    | x    | 
|  4 | x    | x    | y    | 
| 13 | y    | x    | y    | 
| 18 | z    | x    | y    | 
|  5 | x    | x    | z    | 
| 14 | y    | x    | z    | 
| 19 | z    | x    | z    | 
+----+------+------+------+
10 rows in set (0.00 sec)

Let's say I have the row with id 4. So, I know that I'm at id 4 and that the condition is b = 'x' and the order is c. I want to get the next row (which is id 13) and the previous one (which is id 15). Can I find these from the database through SQL queries, or do I have to retrieve everything and loop through it in my code?

Answer 1

You can use the limit clause to do this.

But there doesn't seem to be any point in doing this in sql when the data is already in PHP. Why not put the rows into an array with an element per row, and use the array's pointer to achieve this?

The prev(), current() and next() functions should be all you need.

Answer 2

select * from ps where b = 'x' order by c limit 4, 1;

will get you the 5th (4 + 1) element, which is the one with id 4.

The next could be likewise be fetched by:

select * from ps where b = 'x' order by c limit 5, 1;

That is of course if you really want to read the entries one at a time from the database.

Answer 3

I've done this in the past with queries(to use your example) like:

SELECT id FROM ps WHERE id>'4' ORDER BY c LIMIT 1

For the next id and for the previous id:

SELECT id FROM ps WHERE id < '4' ORDER BY c LIMIT 1

This method obviously requires extra queries but I've really never found a good way to do it.