Php enables up/down arrow sorting

There is such a requirement that there is a list with an option of operation order, which is sorted by arrows. Click the up arrow to increase the order. Click the down arrow to drop the order. The specific functions are as follows: I originally wanted to use the time and order fields to control...

There is such a requirement that there is a list with an option of operation order, which is sorted by arrows.
Click the up arrow to increase the order. Click the down arrow to drop the order.
The specific functions are as follows:

I originally wanted to use the time and order fields for control, click up, order + 1, and update the next sorting time at the same time.
In SQL, sort by order first and then by time.
However, I found that if a new project is created and the initial sorting is 0, clicking the first entry will change order to-1 and rank it to the last one.

Do you have any better implementation methods.

PS: A friend said that clicking an arrow will re-update the order of all entries, but the efficiency is too low. I gave up this method.
Supplement: Thank you very much for your enthusiasm. You are basically talking about the same feasible method. (unfortunately, only one answer can be adopted, otherwise I will all adopt it .)
To sum up my method, I use id as order when inserting data, so that order is a unique field. The next step is to exchange values with the order of the closest row of data.

Reply content:

There is such a requirement that there is a list with an option of operation order, which is sorted by arrows.
Click the up arrow to increase the order. Click the down arrow to drop the order.
The specific functions are as follows:

I originally wanted to use the time and order fields for control, click up, order + 1, and update the next sorting time at the same time.
In SQL, sort by order first and then by time.
However, I found that if a new project is created and the initial sorting is 0, clicking the first entry will change order to-1 and rank it to the last one.

Do you have any better implementation methods.

PS: A friend said that clicking an arrow will re-update the order of all entries, but the efficiency is too low. I gave up this method.
Supplement: Thank you very much for your enthusiasm. You are basically talking about the same feasible method. (unfortunately, only one answer can be adopted, otherwise I will all adopt it .)
To sum up my method, I use id as order when inserting data, so that order is a unique field. The next step is to exchange values with the order of the closest row of data.

When the order field inserts data, it is set to the current time. Then, when a record point (bottom) is located, find the first (Minimum deviation) record with the order less than (greater, exchange their order values

In this way, no matter how long the list is, the cost of each switch is a select where order <xx limit 1 (INDEX) and two update

Since the order field is in order, the update time is unnecessary.

Click "order = n" next to the line to cut the header up:

Swaps the order value of order = n with the order = n-1

Click the downward scissors next to order = n:

Swaps the order values of order = n with order = n + 1.

Method 1: sort the field unique, which is replaced by "proximity replacement" during upgrading/downgrading ". This disadvantage is that if you want to insert a record to follow a row, a record will be rearranged. The worst case is the full table, which is ideally left blank. We recommend that you use RAND to obtain the unique value, which can generate null values. In addition, it is best to add things during rearrangement to prevent operations by multiple people from generating strange things.
Method 2: two fields are sorted. The sorting fields can be repeated. Additional fields are used to determine the sorting of equivalent "sorting fields.
Method 3: The sorting field is the string XXXBBB, XXX is the primary order field, and BBB is the secondary sorting field.

PHP works with JQUERY to optimize interaction. For example, AJAX is sent back to PHP after JQUERY sorting, and PHP performs sorting or exchange sorting.

Tip: When is it? sort by up and down arrows, and use js to drag and sort.