How to update "sorting_order" column of other rows when changing one?

Question

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->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]);  

(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:

<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]);
}
?>

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?

Answer 1

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 >= $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 < $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";

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    |
    +-------------------------+

Answer 2

Ordering should be performed with step greater than one. I prefer the step equal to 10 for better visual representation. Main idea that new item always can be inserted with SO=N+1 that is normally unused. Say, you have inserted something in between 30 and 40, then your so sequence now look like that: 10-20-30-31-40-50.

Then you have to UPDATE the table that way:

SET @cnt = 0;
UPDATE table AS w
   SET w.so = @cnt := @cnt + 10
 ORDER BY w.so ASC
;

The result of UPDATE is that so field is filled by evenly distributed numbers with step of 10: 10-20-30-40-50-60. Now 31 becomes 40, 40 becomes 50 and so forth.

Indeed it is better to incapsulate all the insertion/renumeration into the START TRANSACTION .. COMMIT; for sake of data consistency. Two simultaneous INSERTS with the same so=N+1 can drive to problems. Also an UNIQUE index is recommended for so field.

Later when you fetch the data from the table you have to divide so values by 10 to get the plain sequence of the natural numbers:

SELECT w.so / 10 AS so
     , ... 
  FROM table AS w
 ORDER BY w.so
;