php MYSQL - 通过 ID 获取下一条和上一条记录 - 用于超链接的 HTML
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11194862/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL - Get Next and Previous Record by ID - HTML for hyperlinks
提问by antiquarichat
So I'm trying to add a little bit of convenience to a CRUD by adding next and previous links to navigate between records in my database.
因此，我试图通过添加下一个和上一个链接来在我的数据库中的记录之间导航，从而为 CRUD 添加一些便利。
Here are my queries:
以下是我的疑问：
$id=$_GET['id'];
$id = $currentid;
$prevquery= "SELECT * FROM inventory WHERE id < $currentid ORDER BY id DESC LIMIT 1";
$prevresult = mysql_query($prevquery);
$nextquery= "SELECT * FROM inventory WHERE id > $currentid ORDER BY id ASC LIMIT 1";
$nextresult = mysql_query($nextquery);
?>
Here is my HTML:
这是我的 HTML：
<a href="http://www.url.com/crud/edit.php?id=<?php echo $prevresult; ?> ">Previous</a>
<a href="http://www.url.com/crud/edit.php?id=<?php echo $nextresult; ?> ">Next</a>
Now I tested these queries in PHPMyAdmin and they produced the result I wanted, but I can't get my hyperlinks to actually be supplied with the correct IDs... they're just blank after the =. What am I doing wrong?
现在我在 PHPMyAdmin 中测试了这些查询，它们产生了我想要的结果，但我无法让我的超链接实际提供正确的 ID……它们在 = 之后是空白的。我究竟做错了什么？
回答by jedwards
mysql_query() returns a result set (resource). To get the actual rows from the result set, you need to use a function like mysql_fetch_row().
mysql_query() 返回一个结果集（资源）。要从结果集中获取实际行，您需要使用类似mysql_fetch_row().
Your code for the "next" link would look something like:
“下一个”链接的代码如下所示：
PHP
PHP
$nextquery= "SELECT * FROM inventory WHERE id > $currentid ORDER BY id ASC LIMIT 1";
$nextresult = mysql_query($nextquery);
if(mysql_num_rows($nextresult) > 0)
{
$nextrow = mysql_fetch_row($nextresult);
$nextid = $nextrow['id'];
}
HTML
HTML
<a href="http://www.url.com/crud/edit.php?id=<?php echo $nextid; ?> ">Next</a>
and the previous link would be done similarly.
上一个链接将类似地完成。
Obligatory note:For new code, you should seriously consider using PDO.
强制说明：对于新代码，您应该认真考虑使用PDO。
Advanced note: You could combine your queries into a single query like:
高级说明：您可以将查询合并为一个查询，例如：
SELECT
(
SELECT id
FROM inventory WHERE id < $currentid ORDER BY id DESC LIMIT 1
) AS previd,
(
SELECT id
FROM inventory WHERE id > $currentid ORDER BY id ASC LIMIT 1
) AS nextid
And then adjust the logic accordingly.
然后相应地调整逻辑。
回答by antiquarichat
Okay, It took a little bit but I figured it out...
好吧，花了一点时间，但我想通了......
PHP
PHP
$prevquery= "SELECT * FROM inventory WHERE id < $currentid ORDER BY id DESC LIMIT 1";
$prevresult = mysql_query($prevquery) or die(mysql_error());
while($prevrow = mysql_fetch_array($prevresult))
{
$previd = $prevrow['id'];
}
$nextquery= "SELECT * FROM inventory WHERE id > $currentid ORDER BY id ASC LIMIT 1";
$nextresult = mysql_query($nextquery) or die(mysql_error());
while($nextrow = mysql_fetch_array($nextresult))
{
$nextid = $nextrow['id'];
}
HTML
HTML
<a href="http://www.theantiquarium.com/crud/edit.php?id=<?php echo $previd; ?>">Previous</a>
<a href="http://www.theantiquarium.com/crud/edit.php?id=<?php echo $nextid; ?>">Next</a>
Thanks for the help, I think it put me on the right course. I'll look into that PDO stuff for the future. I'm just now starting to get a hang of the old MYSQL/PHP syntax and now all the sudden there's a new format... tough to keep up with it all!
感谢您的帮助，我认为这让我走上了正确的道路。我会为未来研究 PDO 的东西。我刚刚开始掌握旧的 MYSQL/PHP 语法，现在突然出现了一种新格式……很难跟上这一切！
回答by Bülent ?zden
The resultant code has a culprit at the very beginning and end of the table. Your code will "die" as you ordered. Instead you might check the query results ($prevresult, $nextresult) and decide NOT to show a link for previous or next item.
生成的代码在表的开头和结尾都有一个罪魁祸首。您的代码将在您订购时“死亡”。相反，您可能会检查查询结果 ($prevresult, $nextresult) 并决定不显示前一项或下一项的链接。
回答by user5478919
SELECT IFNULL( (SELECT id FROM inventory WHERE id < $currentid ORDER BY id DESC LIMIT 1 ) , (SELECT id FROM inventory ORDER BY id DESC LIMIT 1 ) ) AS previd , IFNULL( (SELECT id FROM inventory WHERE id > $currentid ORDER BY id ASC LIMIT 1 ), (SELECT id FROM inventory ORDER BY id ASC LIMIT 1 ) ) AS nextid
SELECT IFNULL( (SELECT id FROM inventory WHERE id < $currentid ORDER BY id DESC LIMIT 1 ) , (SELECT id FROM inventory ORDER BY id DESC LIMIT 1 ) ) AS previd , IFNULL((SELECT id FROM inventory WHERE id > $currentid ORDER BY id ASC LIMIT 1 ), (SELECT id FROM inventory ORDER BY id ASC LIMIT 1 ) ) AS nextid
