How to let you change the order of products?

Question

On the database there is this table:

products:

id	name	type
1	product 1	1
2	product 2	2
3	product 3	2
4	product 4	1
5	product 5	1
6	product 6	1
7	product 7	2

the select to extract them is:

select * from products order by id

This select sorts by id, so I will get this result:

But now they want that the administrator can change the order, so he can set a custom order. For example this:

As you can see it's a custom order, it doesn't sort by id but it is decided by the administrator, and I want that this order remains for every user, so it shouldn't be lost when page is refreshed.

Which is the best way to implement this?

Answer 1

But now they want that the administrator can change the order, so he can set a custom order.

Which is the best way to implement this?

You would want to add another column to your products table called sortOrder. It would be a numerical column, like an INT.

You can then add a sort order number for each product in the order you want them to sort. You should space the numbers apart in case you need to insert any products in between two existing products sort order. E.g. by 10s or 100s.

Answer 2

As you can see it's a custom order, it doesn't sort by id but it is decided by the administrator, and I want that this order remains for every user, so it shouldn't be lost when page is refreshed.

There is another way . You can use FIELD() function in the order by .

select p.id,
       p.name,
       p.type
from products p 
order by field (id,5,1,6,4,3,7,2);

Result:

id  name       type
5   product 5   1
1   product 1   1
6   product 6   1
4   product 4   1
3   product 3   2
7   product 7   2
2   product 2   2

Now the order pattern can be changed by the administrator.

I can't think of an automatically way to create order patterns.

Answer 3

if such kind of select is frequent， the best way is to add a column that can satisfy your requirement (an int column and set the number that you need), and add an index on it, to make sure you don't need to "sort file" every time you need.