How to make order of goods?

Question

PHP

Good afternoon. The question is simple, but has many solutions. What do you think is the best way to make the order of products in the catalog.
That is, there is a standard order in the database:
id: 1 Product: 1
id: 2 Product: 2
id: 3 Product: 3
But there is a need to rearrange the records in places.
Let's say that the first product becomes the second, the second the 3rd, and the 3rd the first.
At the same time, the goods are divided into categories, which means that within each category the order specified for it must be observed.

Answer 1

add a sortorder column and sort by it.

Answer 2

1. As advised above, add a column (my name is priority - a matter of taste).
2. We define it, for example, as decimal(6,3) - that is, a number with three decimal places.
3. For new objects, fill it with the value max(priority)+1 - insertion at the end.
4. When the user decides to drag the object to another position, we get the values ​​of the previous and next elements. If there is no previous, we consider it as 0, if there is no next - for the previous +1. We write for the sorted object priority = (prev + next) / 2.
5. If we drag back and forth a lot of times, we can get a situation where neighboring priorities differ by 0.001 - we handle this case in the code, recalculating the priorities so as to increase the interval. Or we just reindex everything entirely in the background with some reasonable regularity.
Thus, for sorting, we need to select only two objects with one request and change only one row.
Instead of fractional values, you can stupidly add new ones with a large step - max(priority)+1000.