PHP mysql排序行 [英] PHP mysql ordering rows
问题描述
例如,我有一个看起来像这样的表:
For example, I have a table which looks like this :
id | 名称
1 |迈克
2 |亚当
3 |约翰
4 | Sarah
...
4 | Sarah ...
现在,当我执行查询 select *从表顺序中按id desc
它将输出如下内容:
Now, when I execute query select * from table order by id desc
it will output something like this:
4 |莎拉
3 |约翰
2 |亚当
1 | Mike
现在,如果我想上下移动John的行,或者上下移动Adam的行(使用MySQL查询(我需要一个基本的查询,只是知道从哪里开始))。
Now what do I do if I want to move John's row up or down, or move Adam's row up or down ( with a MySQL query ( I need basic one, just to know from where to start )).
我的解决方案:
首先,我创建了另一个名为 orderID
的列,该列的值与id相同。
First of all, I created another column named orderID
which has the same value as id.
这里是一个示例向上移动用户:
Here is an example which moves up a user:
$query = "
SELECT (
SELECT orderID
FROM test WHERE id = 'user id that i want to move up'
) AS user_order,
(
SELECT orderID
FROM test WHERE orderID > user_order
ORDER BY orderID
LIMIT 0,1
) AS nextUser_order
";
$result = mysql_query($query);
$data = mysql_fetch_assoc($result);
$query = "
UPDATE test SET orderID = IF(orderID='{$data[nextUser_order]}',
'{$data[user_order]}', '{$data[nextUser_order]}')
WHERE orderID IN ('{$data[nextUser_order]}', '{$data[user_order]}');
";
$result = mysql_query($query);
是否有更好的方法?
推荐答案
您必须切换ID,或通过另一列对其进行排序。那是唯一的方法。
You have to switch IDs, or to order it by another column. That's the only way.
这篇关于PHP mysql排序行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!