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?
解决方案
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.
350
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
