MySQL根据id order by listorder获取上一条和下一条记录

Question

我正在尝试使用 php 和 mysql 创建下一个和上一个导航。我的问题是我无法让它发挥作用。这是我迄今为止尝试过的:

查询:

select * from blog_entry where deleted='n' and isDisplayed='y' and b_id = (select max(b_id) from blog_entry where deleted='n' and isDisplayed='y' and b_id < 154) order by listorder desc

表格

因此，在我的页面中，如果我位于条目号 154( b_id: 154 )，单击下一步按钮将显示 b_id 152 的内容，与上一条记录相同。提前致谢! :)

Answer 1

下面的查询将为您提供第 153 号记录

select * from blog_entry where deleted='n' and isDisplayed='y' and b_id = (select max(b_id) from blog_entry where deleted='n' and isDisplayed='y' and b_id < 154) order by listorder desc limit 1

下面的查询将为您提供第 155 号记录

select * from blog_entry where deleted='n' and isDisplayed='y' and b_id = (select max(b_id) from blog_entry where deleted='n' and isDisplayed='y' and b_id > 154) order by listorder  limit 1