我有一个页面列出客户:

/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/

10-11 06:34
查看更多