更改行時如何更新其他行的“sorting_order”列？

添加新類別和更改現有類別時，我需要為類別設置自定義順序。我相信程式碼和sql查詢在這兩種情況下都是一樣的。在我的webshop_categories-table 中，我有一個so-column（排序順序 INT），用於確定應將類別輸出到查看器的順序。

這是我的類別表的簡化範例（so =sorting_order）：

id  |   name   |  so
--------------------
1   |   Shoes  |  1
2   |   Hats   |  2
3   |   Bags   |  3
4   |   Coats  |  4
5   |   Rings  |  5

添加新類別時，無需定義排序順序，我只需送出一個表單，然後執行如下查詢：

$so = $dbh->query('SELECT (MAX(so)+1) FROM webshop_categories WHERE so != 0')->fetchColumn();
$ins = $dbh->prepare('INSERT INTO webshop_categories (name, so)')->execute([$_POST['name'], $so]);  

（這將添加一個新類別，並將其放在最後。在這種情況下，so將設置為 6）

這是添加新類別的最基本方法。工作正常。但在大多數情況下，在處理類別時，需要將其按特定順序進行呈現。

但通常當添加一個新類別時，人們會想要指定這個新類別相對於現有類別的位置。就在那時，更新so-column 成為我的腦筋急轉彎……

我添加新類別的簡化表格：

<form>
 <input type="text" name="name">
 <select name="so">
   <option value="0" selected>At the end</option>
   <option value="1">At the top</option>
   <!-- php foreach loop through exising categories -->
   <option value="<?=so?>">after <?=category_name?></option>
 <select>
 <input type="submit" name="submit_add" value="Add">
</form>

<?php
if($_POST['submit_add']){
 if($_POST['so']==0){  //  put new category at the end
   $so = $dbh->query('SELECT (MAX(so)+1) FROM webshop_categories WHERE so != 0')->fetchColumn();
 } else {
   $so = $_POST['so'];
 }
 $ins = $dbh->prepare('INSERT INTO webshop_categories (name, so)')->execute([$_POST['name'], $so]);
}
?>

我經歷過第一個場景：沒有指定排序順序。

但是在使用此功能時，我需要更新so受影響的類別。

如果我在 Shoes 和 Hats 之間添加一個新類別，我會在選擇列表中的Shoes 之後使用- 這意味著這個新類別應該獲得2so的值。這意味著有必要對帽子和上述所有類別進行處理。 +1``so

什麼是實現這一目標的好方法？

與此相當的情況是將類別從較高的數字移動到較低的so數字。需要為移動的類別騰出空間，然後將其餘的類別向上（+1對他們的做 a so）。但當然只有在這兩個位置之間的那些。

反過來，它應該非常相似——將一個類別從較低的數字移到較高的數字。唯一的區別是我們現在需要縮小我們在這兩種力量之間創造的差距。

如果我將 Hats 移到after Coats- Hats 將變為 4，其餘的將需要向下碰撞（-1對他們的做 a so）。

我試圖通過在使用此功能時獲取受影響的類別來做到這一點，並在 foreach 循環中更新每個類別。但我無法讓它在所有情況下都按預期工作。

製作所有行的副本（到 PHP 數組中），刪除它們，創建一個更新的新列表，然後將它們插入回來也是一種解決方案，但我認為僅僅進行這些更改就不是必需的。 .但這是我認為有效的一種方法……

有什麼建議嗎？

添加新類別：

   $t_cat = 'jeans';//new category to insert
   $so_to_set = 2;// so for new category

先更新所有so大於等於$so_to_set

   $query = "update categaries set so = so+1 where so >= $so_to_set";

現在插入新category的$so_to_set

   $query = "insert into categaries(name,so) values($t_cat,$so_to_set)";

更改so類別：

假設我們有categaries以下so

   +-------------------------+
   | id  |   name    | so    |
   +-----+-----------+-------+
   | 1   |   shoes   |  1    |
   | 6   |   jeans   |  2    |
   | 2   |   shirts  |  3    |
   | 3   |   pants   |  4    |
   | 4   |   blazzers|  5    |
   | 5   |   bags    |  6    |
   +-------------------------+

   $a = 2;// so of category whose position we want to change
   $b = 5;// new so for category

   // get id of category whose position we want to change
   $temp_id = "select id from categaries where so = $a"; 
   // that would be 6

$a現在在和之間更新所有內容$b

   if($a < $b){
       // if $a is less than $b then
       $set = "so = so - 1";
       $where = "so > $a and so <= $b";
   }else{
       $set = "so = so + 1";
       $where = "so < $a and so >= $b";
   }

   // update so's
   $query = "update categaries set $set where $where";

現在更新所以$temp_id

   $query = "update categaries set so = $b where id = $temp_id";

該過程的最終輸出將是

   +-------------------------+
   | id  |   name    | so    |
   +-----+-----------+-------+
   | 1   |   shoes   |  1    |
   | 6   |   jeans   |  5    |
   | 2   |   shirts  |  2    |
   | 3   |   pants   |  3    |
   | 4   |   blazzers|  4    |
   | 5   |   bags    |  6    |
   +-------------------------+

引用自：https://dba.stackexchange.com/questions/203799