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屋！