How to update "sorting_order" column of other rows when changing one? I need to set a custom order sequence for categories when adding new ones, and changing existing ones. I believe the code and sql query will be the same in both scenarios.
In my webshop_categories-table I have a so-column (sorting order INT) that I uses to determen which order the categories should be outputed to the viewer.

Here's a simplified example of how my categories-table looks like (so = sorting_order):

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


When adding a new category, without defining the sorting order, I would just submit a form, and run a query like this:

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


(This will add a new category, and just put it at the end. In this case the so would be set to 6)

That's the very basic way of just adding a new category. Works fine. But in most cases, when dealing with categories, one need to put it in a specific order for presentation.  

But usually when adding a new category, one would want to specify where this new category should be placed in relation to the existing ones. And that's when updating the so-column becomes a brain twister for me...

My simplified form for adding a new category:

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

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


I've been through the first scenario: Not specifying a sorting order.
But when it comes to using this feature, I need to update the so for the affected categories.
If I add a new category between Shoes and Hats I would use after Shoes in the selection list - which means that this new category should get the so-value of 2. This means that it will be necessary to do a +1 to the so for Hats and all categories above.  

What would be a good way of accomplishing this? 

A quite equal situation to this would be when moving a category from a higher to a lower so number. One would need to make space for the moved category, and bump the rest one up (do a +1 to their so). But only the ones in between the two positions ofcourse.  

And it should be quite similar when doing it the other way around - Moving a category from a lower to a higher number. The only difference being that we now need to close the gap that we created between these two powitions.
If I moved Hats to after Coats - Hats would become 4, and the rest would need to be bumped one down (do a -1 to their so).

I've tried to do this by fetching the affected categories when using this feature, and update each one in a foreach loop. But I couldnt get it to work as expected for all scenarios.  

Making a Copy of the all the rows (into a PHP array), Delete them, Create a new list with updated so, then Inserting them back was also a solution, but I dont think it should be neccesary just to make these changes... But that's one way I figured out that worked...

Any suggestion?
Add new category :

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


First update all so greater than or equal to $so_to_set

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


Now insert new category with the $so_to_set

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



Change so for categories :

suppose we have following categaries with 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


Now update all so between $a and $b

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

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


now update so for $temp_id

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


Final output of the process would be

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