如何在表中添加新条目并更改其他条目的排序顺序

I'm creating a forum application and I have a table that stores the categories. This table has 3 columns:

Category_Id | Category_Name | CategoryOrder

I have a PHP script that adds a new category to the databse and it includes selecting the order in which the categories will display. So, for instance, if I have 5 categories stored in the database and I want to add a new one and make it the number one category in the CategoryOrder column, it means that the CategoryOrder of other categories will have to change.

If I only had one category in the table I would just update it to a new value and assign the 1 value to the new category. But what should I do if I have five rows. How do I change all of them to add 1 to their current value? I don't know if I'm making sense here.

try to increment the CategoryOrder first

update categorytable set CategoryOrder = CategoryOrder + 1

then insert the new record with CategoryOrder = 1

I did this a few times. I couldn't find any code right now so I'll post only an algorithm (with maybe some pseudo code in it):

1. INSERTING a new value (simpler one):
   • update all the categories that have CategoryOrder bigger or the same as the newly inserted one: UPDATE Categories SET CategoryOrder = CategoryOrder + 1 WHERE CategoryOrder >= $newCategory['order'];
   • insert Your new Category
2. UPDATING an existing category:
   • check, whether the new order is bigger or smaller than it was (nothing has to be done if it is the same...)
   • if it is bigger, update all the categories that have CategoryOrder bigger than old order and smaller then new order and subtract 1: UPDATE Categories SET CategoryOrder = CategoryOrder - 1 WHERE CategoryOrder > $category['old_order'] AND CategoryOrder <= $category['new_order']; - by this You fill the gap
   • if it is smaller, do it similar to when it is bigger, but add 1 to those categories that have CategoryOrder >= new_order and CategoryOrder < new_order

This should be it.

EDIT: Explanation on the second case (updating):

Let's say we have these entries:

Category_Id | Category_Name | CategoryOrder
1           | name_1        | 1
2           | name_2        | 2
3           | name_3        | 3
4           | name_4        | 4
5           | name_5        | 5

And now we want to edit category with order 2 and change it to 4. What we have to do is make the categories with order 3 and 4 go down by one:

Category_Id | Category_Name | CategoryOrder
1           | name_1        | 1
2           | name_2        | 2
3           | name_3        | 2
4           | name_4        | 3
5           | name_5        | 5

and finally update the desired category and it's order to 4:

Category_Id | Category_Name | CategoryOrder
1           | name_1        | 1
3           | name_3        | 2
4           | name_4        | 3
2           | name_2        | 4
5           | name_5        | 5

Is it clearer now?

Now what if we want to change category with order 5 to become the first one? Update all the categories that have order >= 1 and 5 < and set the order to +1:

Category_Id | Category_Name | CategoryOrder
1           | name_1        | 2
3           | name_3        | 3
4           | name_4        | 4
2           | name_2        | 5
5           | name_5        | 5

and finally update the desired category:

Category_Id | Category_Name | CategoryOrder
5           | name_5        | 1
1           | name_1        | 2
3           | name_3        | 3
4           | name_4        | 4
2           | name_2        | 5

(note the categories being ordered by their order and id)