MySQL Case语句(如果有的话)

I'm attempting to re-order a list based on their positions upon updating the position of one item via the submission of a drop-down box (which has a name assigned to the PHP variable $position). Here's an example, with positions represented by their order in the list:

在通过提交下拉框(下拉框的名称分配给PHP变量$position)更新一个项目的位置时，我试图根据它们的位置重新排序一个列表。这里有一个例子，在列表中以它们的顺序表示的位置:

1. Item1
2. Item1
3. Item2
4. 第二条
5. Item3
6. Item3
7. Item4
8. Item4
9. Item5
10. Item5

I'm using the following MySQL query:

我使用以下MySQL查询:

UPDATE subjects SET 
position = position + 1 
WHERE position <= position AND position >= {$position};

If I was to move Item5 to position 2 this query would push everything from position 2 to 4 downwards by one position, resulting in:

如果我将Item5移动到位置2，这个查询将把位置2到4的所有项向下移动一个位置，结果是:

1. Item1
2. Item1
3. Item5 <<< moved up
4. Item5 < < <上升< li>
5. Item2
6. 第二条
7. Item3
8. Item3
9. Item4
10. Item4

However, If I wish to do the opposite and move an item downwards, the position would need to become one less (position = position - 1), and the <= and >= signs would swap. Therefore I'm wondering how I would do this in a case argument (an equivalent to if{} else if{})

然而，如果我希望做相反的事情，并向下移动一个项，那么这个位置就需要变成一个更少的位置(位置=位置- 1)，而<=和>=符号互换。因此，我想知道如何在case参数中执行这个操作(相当于if{} else if{})

2 个解决方案

#1

If you need the if else you can use the MySQL case statement (link). It basically works as follows:

如果需要的话，可以使用MySQL case语句(link)。它的基本工作原理如下:

CASE case_value
  WHEN when_value THEN statement_list
  [WHEN when_value THEN statement_list] ...
  [ELSE statement_list]
END CASE

And you can nest them as required.

你可以根据需要对它们进行嵌套。

#2

Plz try it

请试一试

update subjects set position=(CASE
    WHEN search_condition THEN 
        position+1
    WHEN search_condition THEN 
    postion-1
    else
       postion
END CASE) WHERE position <= position AND position >= {$position} AND menu_type = '{$menu_type}'";