Update surrounding rows accordingly to order column

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 sorting_order INT-column that I uses to determen which order the categories should be outputed to the viewer.

Here's a simplified example of my table (so = sorting_order):

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

When adding an unspecified category I would just fill out the form, and submit it like so:
(This will add a new category, and just put it at the end. In this case the so would be set to 6)

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

But from time to time I need to change the order of the categories.
I might need to move Coats to between Shoes and Hats.
That would require an update to the surrounding categories.
In this case; Hats, or anything above wherever I'm moving this category to, would now need to do a +1 to so column to make space for this move. But the increment need to stop at Coats obviously.

And vice-versa. If I move Hats to between Coats and Rings, I would need to do a -1 to Coats and Bags. Again.

What would be a good way of accomplishing this?

The change is made through a form select where in the options are move to top (means 1) and Move to after ?cat_name?.

I believe it will be the same code for when adding a new category with a preset sorting order. The same selection-list with the same options.

Would I need to query and fetch all categories that would need to have the so updated with a +/- 1, and loop through them to make the changes?
Or there might be a way to do a +/- 1 to them all at the same time without looping throught each one?

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, should not be neccesary just to make these changes... But that's one way I figured out that worked...

Answer 1

There are multiple ways to do this but the specific approach really depends on your structure.

That said, however you approach it, you just need to increment or decrement the ones below or above the one you are changing:

When new so value is:

1. less than the current one (from 4 to 2), select all values less than current one up to and including new one (2, 3), and increment them by 1
2. greater than the current one (from 2 to 4), select all values greater than current one up to and including new one (4, 3), and decrement them by 1

I suggest only selecting the rows you need to update, so the logic of "if less than" etc would be in the select query. Then you just use that result array to update.

Example:

1 = shoes
2 = hats
3 = bags
4 = coats
5 = rings

WARNING: This code is very old school and ONLY for quick illustration purposes to show the logic. It is not tested and may have bugs, test before use.

The example code function will change any value greater or less than, eg 4 to 2, OR 2 to 4, etc

// You'd retrieve the requested change data from your form (or whatever)
update_categories_so(
    (int) $_POST['category_id'],
    (int) $_POST['current_so'],
    (int) $_POST['new_so']
);

/**
 * @param int $categoryIdForChangedSo
 * @param int $currentSo
 * @param int $newSo
 *
 * @return void
 */
function update_categories_so($categoryIdForChangedSo, $currentSo, $newSo)
{
    // Determine if incrementing or decrementing
    $increment = $newSo < $currentSo  ? true : false;

    // Set increment or decrement var for the update
    $changeBy = $increment ? '+' : '-';

    // Set the where clause to get the current category_ids
    // which would need to be updated
    $selectWhereClause = $increment
        ? "'so' >= $newSo AND 'so' < $currentSo"
        : "'so' <= $newSo AND 'so' > $currentSo";

    $selectSql = "
        SELECT
            `category_id`
        FROM
            `webshop_categories`
        WHERE
            {$selectWhereClause}
        ";

    // Return the results into $categoryIdsForUpdate

    // Update the categories which are affected by the main change,
    // from the array from the DB
    $updateOtherSoSql = "
        UPDATE
            `webshop_categories`
        SET
            'so' = 'so' {$changeBy} 1
        WHERE
            'category_id' IN ({$categoryIdsForUpdate})
        ";

    // Update the main one being changed
    $updateRequestedSosql = "
        UPDATE
            `webshop_categories`
        SET
            so = {$newSo}
        WHERE
            'category_id' = {$categoryIdForChangedSo}
        ";
}

I don't know how or even if you handle errors, but you could return the outcome - check if queries were ok etc

More info on $selectWhereClause, re how the where works:

• Don't update the one requested to be updated (updated separately)
• If new "so" is less than current, then we increment from new (inc new) to 1 before current
• If new "so" is greater than current, then we decrement from 1 after current to new (inc new)

More info on $categoryIdsForUpdate data which is returned from the DB. Example array below is when changing "so" 4 (Coats) to 2 (currently Hats). (example IDs are not 1-5 to avoid confusion with "so" values)

category_id => so

$categoryIdsForUpdate = [
    10 => 1, // (shoes) not included as is less than new "so"
    13 => 2, // (hats)  incremented by 1
    17 => 3, // (bags)  incremented by 1
    18 => 4, // (coats) not included as is current category_id
    27 => 5, // (rings) not included as greater than current "so"
];