Databases offer a lot of flexibility when dealing with data. By spending some time upfront on the database design, it will save you plenty of headaches later on. Since the object of this to tutorial is not to elaborate on the advantages of using a database, I will skip to its limits.
The operations you can run on a database are always based on logic and numbers. So for example, when you run a store and sell T-Shirts, and you sort by size, “M” will always be sorted before “S”, even though it is not correct from a humanistic point of view.
Assuming we have a table called “Products” in the database, here is a set of data that you would typically deal with and a solution for sorting the data in a more useful way using the MySQL database.
Name | Size T-Shirt | L T-Shirt | M T-Shirt | S T-Shirt | XL T-Shirt | XS
And here is how you typically retrieve records:
SELECT Name, Size FROM Products ORDER BY Size
This query returns all products, however, not sorted “XS, S, M, L, XL” but in “L, M, S, XL, XS”.
The solution to this problem is a simple CASE (see: Control Flow Functions):
SELECT Name, Size, CASE Size WHEN XS THEN 1 WHEN S THEN 2 WHEN M THEN 3 WHEN L THEN 4 WHEN XL THEN 5 END AS Sort_Order FROM Products ORDER BY Sort_Order ASC
This returns the products in order of their sizes. Use ASC (ascending) or DESC (descening) to change the order from smallest size to largest size or vice-versa.
That’s it, enjoy.
Post Your Comment