基于名称的下一个和上一个MySQL行 [英] Next and Previous MySQL row based on name
问题描述
我有一张桌子，上面有有关人员的详细信息.我想根据个人的姓氏创建一个下一个/上一个链接.由于人员不是按字母顺序添加的，因此无法根据其ID选择下一行或上一行.
I have a table with details on personnel. I would like to create a Next/Previous link based on the individual's last name. Since personnel were not added in alphabetical order, selecting the next or previous row based on its ID does not work.
这是一个庞大的表-相关字段是id，name_l和name_f.我想按个人的姓氏name_l排序.
It is a hefty table - the pertinent fields are id, name_l, and name_f. I would like to order by name_l, the individuals' last name.
我将如何完成这项任务?
How would I go about accomplishing this task?
谢谢！
修改 这将在人员详细信息"页面上使用，结果将基于当前行生成指向数据库中的下一个/上一个条目的链接(按姓氏排序).例如，如果我正在查看Joe Hammer，则下一步"链接将链接到弗兰克·英格拉姆.
Edit This will be used on a Personnel Details page, the result will generate links to the next/prev entry in the database (ordered by last name) based on the current row. For example, if I am viewing Joe Hammer, the Next link would link to Frank Ingram.
最终代码
感谢Daniel，这是我终于要工作的地方:
Thanks to Daniel, here is what I finally got to work:
首先，我将增量设置为0: $ i = 0 .然后，在使用 while 循环遍历记录的同时，我将其增加了1 = $ i ++ .然后，我链接到该特定条目的详细信息页面:
First, I set an increment at 0: $i = 0. Then, while looping through records with a while loop, I increased this by 1 = $i++. I then made a link to the details page for that particular entry:
<a href="details.php?id=<?php echo $member['id'];?>&row=<?php echo $i;?>">Details</a>
在详细信息"页面上，我使用以下SQL选择下一条记录:
On the Details page, I used the following SQL to select the next record:
$row = $_GET['row'];
$getNext = mysql_query("SELECT * FROM members ORDER BY name_l, id LIMIT ".$row.", 1");
$next = mysql_fetch_assoc($getNext);
$nextLink = $row + 1;
最后，链接:
<a href="member_details.php?id=<?php echo $next['id'];?>&row=<?php echo $nextLink;?>"><?php echo $next['name_l'] . ", " . $next['name_f'];?></a>
推荐答案
首先，请确保您的name_l列已建立索引.然后，您可以简单地使用ORDER BY和LIMIT子句，如下所示:
First of all, make sure that your name_l column is indexed. Then you can simply use the ORDER BY and the LIMIT clauses as follows:
SELECT * FROM personnel ORDER BY name_l, id LIMIT 0, 1;
只需增加LIMIT子句中的0值，即可选择有序集中的下一条记录.因此，使用LIMIT 1, 1获取第二条记录，使用LIMIT 2, 1获得第三条记录，等等.
Simply increment the 0 value in the LIMIT clause to select the next record within the ordered set. Therefore use LIMIT 1, 1 to get the second record, LIMIT 2, 1 for the third, etc.
要在name_l上创建索引，可以使用 CREATE INDEX 命令:
To create an index on name_l you can use the CREATE INDEX command:
CREATE INDEX ix_index_name ON personnel (name_l);
测试用例:
CREATE TABLE personnel (
id int not null primary key,
name_l varchar(10),
name_f varchar(10)
);
CREATE INDEX ix_last_name_index ON personnel (name_l);
INSERT INTO personnel VALUES (1, 'Pacino', 'Al');
INSERT INTO personnel VALUES (2, 'Nicholson', 'Jack');
INSERT INTO personnel VALUES (3, 'De Niro', 'Robert');
INSERT INTO personnel VALUES (4, 'Newman', 'Paul');
INSERT INTO personnel VALUES (5, 'Duvall', 'Robert');
结果:
SELECT * FROM personnel ORDER BY name_l, id LIMIT 0, 1;
+----+---------+--------+
| id | name_l | name_f |
+----+---------+--------+
| 3 | De Niro | Robert |
+----+---------+--------+
1 row in set (0.00 sec)
SELECT * FROM personnel ORDER BY name_l, id LIMIT 1, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
| 5 | Duvall | Robert |
+----+--------+--------+
1 row in set (0.00 sec)
SELECT * FROM personnel ORDER BY name_l, id LIMIT 2, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
| 4 | Newman | Paul |
+----+--------+--------+
1 row in set (0.00 sec)
第一次更新:(进一步评论如下)
1st UPDATE: (Further to the comments below)
上面的示例主要适用于以下情况:首先显示第一个记录，然后依次移至下一个，然后再移至下一个，再向后移，依此类推.您还可以轻松地向前移动x步和向后移动x步，但这似乎不是必需的.
The above example is suitable mainly if you start by displaying the first record, and then you move sequentially to next, and maybe next again, and perhaps one back, etc. You could also easily move x steps forward and x steps backwards, but this does not appear to be required.
但是，如果您从随机记录开始，将更难适应上面的查询以获取下一个和上一个记录.
If you start from a random record however, it will be more difficult to adapt the query above to get the next and previous records.
如果是这种情况，那么您只需保留当前位置的索引计数器即可.您从$index = 0开始，并使用... LIMIT $index, 1显示第一条记录.然后，通过将$index加1来显示下一个，并且通过将$index减1来显示前一个.
If this is the case, then you can simply keep an index counter of where you currently stand. You start with $index = 0, and display the first record by using ... LIMIT $index, 1. Then you display the next by incrementing $index by 1, and you display the previous by decrementing $index by 1.
如果另一方面，您将呈现人员列表，然后用户将单击一个记录(随机)，您也可以在应用程序端(php)的帮助下完成此工作.假设您将以下有序列表呈现给用户:
If on the other hand, you will be rendering the personnel list, and then the user will click on one record (at random), you could also make this work with some help from the applications-side (php). Let's say you render the following ordered list to the user:
+----+-----------+--------+
| id | name_l | name_f |
+----+-----------+--------+ // [Hidden info]
| 3 | De Niro | Robert | // Row 0
| 5 | Duvall | Robert | // Row 1
| 4 | Newman | Paul | // Row 2
| 2 | Nicholson | Jack | // Row 3
| 1 | Pacino | Al | // Row 4
+----+-----------+--------+
现在，如果用户单击Newman Paul，则必须将row=2参数传递到显示该员工详细信息的页面.现在，呈现员工详细信息的页面知道纽曼·保罗(Newman Paul)是第三行(row=2).因此，要获取上一条和下一条记录，您只需将LIMIT x, 1中的x更改为上一个记录的row - 1，将下一个记录的row + 1更改为
Now if the user clicks on Newman Paul, you would have to pass the row=2 parameter to the page that will display the details of this employee. The page that renders the details of the employee now knows that Newman Paul is the 3rd row (row=2). Therefore to get the previous and the next records you would simply change the x in LIMIT x, 1 by row - 1 for the previous record and row + 1 for the next:
-- Previous
SELECT * FROM personnel ORDER BY name_l, id LIMIT 1, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
| 5 | Duvall | Robert |
+----+--------+--------+
1 row in set (0.00 sec)
-- Next
SELECT * FROM personnel ORDER BY name_l, id LIMIT 3, 1;
+----+-----------+--------+
| id | name_l | name_f |
+----+-----------+--------+
| 2 | Nicholson | Jack |
+----+-----------+--------+
1 row in set (0.00 sec)
第二次更新:
您可以使用以下特定于MySQL的查询来获取任何随机雇员的有序列表中的记录号，然后可以使用该记录号获取上一条和下一条记录.但是请注意，这不是很有效，并且如果您有成千上万的记录，则可能会降低性能.
You can use the following MySQL-specific query to get the record number within the ordered list of any random employee, which then can be used to get the previous and next records. Note however that this is not very efficient, and may degrade performance if you have thousands of records.
假设您在员工Nicholson Jack中.
Let's say you are in employee Nicholson Jack.
您可以执行以下查询:
SELECT p.id, p.name_l, p.name_f, o.record_number
FROM personnel p
JOIN (
SELECT id,
@row := @row + 1 AS record_number
FROM personnel
JOIN (SELECT @row := -1) r
ORDER BY name_l, id
) o ON (o.id = p.id)
WHERE p.name_l = 'Nicholson' AND p.name_f = 'Jack';
哪个返回此:
+----+-----------+--------+---------------+
| id | name_l | name_f | record_number |
+----+-----------+--------+---------------+
| 2 | Nicholson | Jack | 3 |
+----+-----------+--------+---------------+
1 row in set (0.00 sec)
请注意，在WHERE子句中，如果已知ID，则可以使用p.id = 2，而不是p.name_l = 'Nicholson' AND p.name_f = 'Jack'.
Note that in the WHERE clause you could have used p.id = 2 if the id is known, instead of p.name_l = 'Nicholson' AND p.name_f = 'Jack'.
现在，我们可以使用record_number字段(在这种情况下为3)来获取上一条和下一条记录，只需使用此答案顶部的原始查询，并将LIMIT 2, 1替换为前一个，后一个LIMIT 4, 1.你去了
Now we can use the record_number field, which is 3 in this case, to get the previous and the next records, simply by using the original query from the top of this answer, and replacing LIMIT 2, 1 for the previous and LIMIT 4, 1 for the next. There you go:
尼科尔森·杰克(Nicholson Jack)的前任:
The previous of Nicholson Jack:
SELECT * FROM personnel ORDER BY name_l, id LIMIT 2, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
| 4 | Newman | Paul |
+----+--------+--------+
1 row in set (0.00 sec)
Nicholson Jack的下一个:
The next from Nicholson Jack:
SELECT * FROM personnel ORDER BY name_l, id LIMIT 4, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
| 1 | Pacino | Al |
+----+--------+--------+
1 row in set (0.00 sec)
这篇关于基于名称的下一个和上一个MySQL行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
