How do I define custom sort on a DB return?

Question

How do I define custom sort on a DB return?

Navigation

#1 by (3 votes)

2

Today I have a simple product catalog that shows all products together or by category.

However, when I show all together I can only sort by ASC or DESC, but I need to sort it differently. When I show everyone I want to see a product X first or at the position I set , not a simple ASC or DESC, because when I add a Y, or it appears at the beginning (sorting id by DESC) or at the end (sorting id by ASC).

I would like to implement in my code (PHP + MySQL). I saw an example here: link (in this case the administrator will choose where the product appears, which can go up or to descend).

If the post is a bit confusing, I'm happy to explain it.

php mysql

asked by anonymous 28.05.2016 / 19:32

1 answer

Implement a vector that accepts anything List most borrowed books

score 3 · 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 SELECT * FROM produtos ORDER BY ordem ASC/DESC ... , just like you do with the id. That is, when you insert / edit the product, you enter a new data, which is where (1 for the first, 2 for the second, 3 for the third ...) where you want it to be. >

Please note that if you do not want products with equal places ( ordem ) you have to work on your logic to:

INSERT

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

EDITION: Uploading from place (eg from place 7 to place 2)

Here the operation will be slightly different, if the product is in seventh and we want to change it to be 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 are going to be incremented by 1 in the ordem column. 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 (eg from place 2 to place 7)

Here, following the opposite logic of place-elevation, we make all products whose ordem > 2 and ordem <= 7 are going to be DECREMENTED in 1 in the ordem column. We make UPDATE produtos SET ordem = ordem - 1 WHERE ordem > 2 AND ordem <= 7 and soon to update the ordem of the product to 7 ( UPDATE produtos SET ordem = 7 WHERE id = ID_SEGUNDO_PRODUTO ).

DELETE

Here we have to decrease by 1 to ordem of all products whose ordem is higher than the order of the product 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 we delete the product DELETE FROM produtos WHERE id = ID_SETIMO_PRODUTO

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