php - 基于名称的下一个和上一个 MySQL 行

Question

我有一张包含人员详细信息的表格。我想创建一个基于个人姓氏的下一个/上一个链接。由于人员未按字母顺序添加，因此无法根据 ID 选择下一行或上一行。

这是一个很大的表 - 相关字段是 id、name_l 和 name_f。我想按 name_l(个人的姓氏)排序。

我将如何完成这项任务？

谢谢!

编辑这将在人员详细信息页面上使用，结果将根据当前行生成指向数据库中下一个/上一个条目的链接(按姓氏排序)。例如，如果我正在查看 Joe Hammer，则 Next 链接将链接到 Frank Ingram。

最终代码

感谢 Daniel，这是我终于开始工作的地方:

首先，我将增量设置为 0:$i = 0。然后，在使用 while 循环遍历记录时，我将其增加 1 = $i++。然后我链接到该特定条目的详细信息页面:

<a href="details.php?id=<?php echo $member['id'];?>&amp;row=<?php echo $i;?>">Details</a>

在详细信息页面上，我使用以下 SQL 来选择下一条记录:

$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'];?>&amp;row=<?php echo $nextLink;?>"><?php echo $next['name_l'] . ", " . $next['name_f'];?></a>

Answer 1

首先，确保您的name_l 列已编入索引。然后您可以简单地使用 ORDER BY 和 LIMIT 子句，如下所示:

SELECT * FROM personnel ORDER BY name_l, id LIMIT 0, 1;

只需增加 LIMIT 子句中的 0 值即可选择有序集中的下一条记录。因此，使用 LIMIT 1, 1 获取第二条记录，使用 LIMIT 2, 1 获取第三条记录，等等。

要在 name_l 上创建索引，您可以使用 CREATE INDEX命令:

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)

---

第一次更新:(关于下面的评论)

上面的例子主要适用于你从显示第一条记录开始，然后你依次移动到下一条，也许再下一条，也许后退一条，等等。你也可以很容易地向前移动 x 步，向后移动 x 步，但这似乎不是必需的。

但是，如果您从随机记录开始，则调整上述查询以获取下一条和上一条记录会更加困难。

如果是这种情况，那么您只需保留一个您当前所处位置的索引计数器即可。您从 $index = 0 开始，然后使用 ... LIMIT $index, 1 显示第一条记录。然后通过将 $index 递增 1 来显示下一个，通过将 $index 递减 1 来显示上一个。

另一方面，如果您要呈现人员列表，然后用户将(随机)单击一条记录，您也可以在应用程序端 (php) 的帮助下完成这项工作。假设您向用户呈现以下有序列表:

+----+-----------+--------+
| 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 是第 3 行 (row=2)。因此，要获得上一个和下一个记录，您只需将 LIMIT x, 1 中的 x 更改为上一个记录的 row - 1 并且行 + 1 为下一个:

-- 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 的查询来获取任何随机员工的有序列表中的记录编号，然后可以使用它来获取上一条和下一条记录。但是请注意，这不是很有效，如果您有数千条记录，可能会降低性能。

假设您是员工 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)

请注意，如果 id 已知，您可以在 WHERE 子句中使用 p.id = 2，而不是 p.name_l = 'Nicholson' AND p.name_f = 'Jack'.

现在我们可以使用 record_number 字段，在本例中为 3，只需使用顶部的原始查询即可获取上一条和下一条记录这个答案，并替换 LIMIT 2, 1 为前一个和 LIMIT 4, 1 为下一个。给你:

尼科尔森· 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 的下一个:

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)