How do I define custom sorting on the return of a DB?

Question

Today I have a simple product catalogue (images) that shows all products together or by categories.

However, when I show all together I can only order by ASC or DESC, but I need to order differently. When I show everyone I want an X product to appear first or in the position I define, not a simple ASC or DESC, because when I add a Y, either it appears at the beginning (ordering id by DESC) or at the end (ordering id by ASC).

I would like to implement in my code (PHP+Mysql). I saw an example here: http://mateusgamba.xpg.uol.com.br/ordenar_tabela.html (in case the administrator will choose where the product appears, which can go up or down).

In case the post is a bit confused I am willing to explain better.

Answer 1

To do this you have to store the custom order you want. You can create a column in your table with the order (int), and then sort from there with the SELECT * FROM produtos ORDER BY ordem ASC/DESC ..., as it does with id. That is, when you insert/edit the product you insert one more piece of data, which is where (1 for the first, 2 for the second, 3 for the third ...) you want it to stay.

Note that with this, if you do not want there to be products with places (ordem) have to work out their logic to:

INSERTION

So if you have a product with seat 5 (ordem is 5), but if later will insert another that you think should stay in place 5 will have to increase +1 to ordem of all products in the order >= 5, (UPDATE produtos SET ordem = ordem + 1 WHERE ordem >= 5) and then inserts the new product. In this example the first one that was in place 5 will now be in place 6, 6 -> 7, 7 -> 8 etc...

EDITION: Place climb (ex: from place 7 to place 2)

Here the operation will be a little different, if the product is in seventh and we want to change so that it stays in second we make all products whose ordem is between 2 and 7 change the number of the place where they appear. That is, only the products of ordem < 7 and ordem >= 2 is that they will be incremented by 1 in the column ordem. We make UPDATE produtos SET ordem = ordem + 1 WHERE ordem >= 2 AND ordem < 7, and then we update the ordem of the product from 7 to 2 (UPDATE produtos SET ordem = 2 WHERE id = ID_SETIMO_PRODUTO)

EDITION: Descent from place (ex: from place 2 to place 7)

Here, following the opposite logic to the rise of place we make all products whose ordem > 2 and ordem <= 7 is that they will be DECREMENTED in 1 in the column of ordem. We make UPDATE produtos SET ordem = ordem - 1 WHERE ordem > 2 AND ordem <= 7 and right after doing the ordem of the product to 7 (UPDATE produtos SET ordem = 7 WHERE id = ID_SEGUNDO_PRODUTO).

DELETE

Here we have to go down by 1 to ordem of all products whose ordem is superior to the order of the product that we delete. If we want to delete the product that is in seventh we do UPDATE produtos SET ordem = ordem - 1 WHERE ordem > 7 and then finally erase the product DELETE FROM produtos WHERE id = ID_SETIMO_PRODUTO

This meneira ensures that they are always consecutive and listed in the order you want without products with places (ordem) repeated