MySQL – How to Update ‘sorting_order’ Column of Other Rows When Changing One

MySQLPHPsortingupdate

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?

Best Answer

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