我有一个页面列出客户:
/customers/
此页面上的客户顺序不是连续的:
1st: ID 111
2nd: ID 567
3rd: ID 345
etc
这是因为SQL查询使用户可以根据名字,姓氏或昵称选择客户订单。
"SELECT customer_id FROM customers WHERE status = 1 ORDER BY " . $customer_order
因此,当我在上面的示例中成为第二位客户时:
/customer/567/
所以我的问题是我想像这样显示上一个和下一个按钮,但不知道如何:
<a href="111">Prev</a>
<a href="567">Current</a>
<a href="345">Next</a>
我设法使Previous链接在while循环中工作,但这很丑陋。我尝试使用current(),prev()和next(),但失败了。
我在StackOverflow上看到的大多数解决方案都与顺序ID有关,在顺序ID中,它们是当前ID -1或1,或者它们在查询中是“ LIMIT 1”,我相当确定在测试了此情况后,这种情况不可行。
有任何想法吗?
最佳答案
您可以为查询分配行号,并使用该行号标识上一条和下一条记录:
SELECT (@row_number := @row_number + 1) AS row_number, customer_id
FROM customers, (SELECT @row_number := 0) AS rn
WHERE status = 1
ORDER BY firstname
如果您具有当前客户的行号,请使用主查询作为子查询查找下一个/上一个记录:
SELECT customer_id AS next_id
FROM
(
SELECT (@row_number := @row_number + 1) AS row_number, customer_id
FROM customers, (SELECT @row_number := 0) AS rn
WHERE status = 1
ORDER BY firstname
) ordered_customers
WHERE ordered_customers.row_number = :current_row_number + 1
如果您只有当前ID:
SELECT customer_id AS next_id
FROM
(
SELECT (@row_number := @row_number + 1) AS row_number, customer_id
FROM customers, (SELECT @row_number := 0) AS rn
WHERE status = 1
ORDER BY firstname
) ordered_customers
WHERE ordered_customers.row_number = 1 + (
SELECT row_number
FROM
(
SELECT (@row_number2 := @row_number2 + 1) AS row_number, customer_id
FROM customers, (SELECT @row_number2 := 0) AS rn
WHERE status = 1
ORDER BY firstname
) ordered_customers2
WHERE ordered_customers2.id = :current_id
)
关于php - PHP Mysql-从当前客户创建上一个和下一个链接,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/38630482/