基于int的自定义字段排序

Question

If I have a list of database fields:

id    order    title
1     3        This is a post
2     1        This is another post
3     2        This is also a post
4     4        This is still a post

And I want change the order of these based on order.
If I set order on id #1 to 2 I want order on id #3 to be set to 3.
If I set order on id #4 to 1 I want order on id #1 to be 4, order on id #2 to be 2, order on id #3 to be 3 etc. etc.

How can I do this? I think it should be quite simple but I can't figure it out.
I have searched but I don't know what to search for...

Answer 1

You can do it in SQL with two UPDATE statements:

START TRANSACTION;
SELECT order FROM t WHERE order BETWEEN $new_order AND $old_order FOR UPDATE;

UPDATE t SET order = order + 1  WHERE order BETWEEN $new_order AND $old_order;
UPDATE t SET order = $new_order WHERE id = $id;

COMMIT;

I have locked the table during these updates to prevent concurrency issues (however, note that transactions/locks are only applicable to InnoDB tables).