Sort out your records – MySQL style
So I had a predicament. I had to sort out a list of records. Not amazingly difficult, I know, but the client wanted it sorted in a specific order. The Status (which is what I am sorting by) was displayed as text on the front end, but was a number in the database. So my task was to order this list by a non-sequential order that I can define.
As is usual, I played around a bit before I got it working. Then I started looking for a better (and non code based) way to go about it. The answer came in the form of the ORDER BY FIELD() MySQL function.
Usually, your ordered statement looks something like this:
SELECT * FROM where_ever ORDER BY Column1, Column2
Simple, but not helpful in this case. But adding in the ORDER BY FIELD command, our SQL statement looks like this:
SELECT * FROM where_ever ORDER BY FIELD(fieldToOrderBy,4,1,5,6)
So now when you display your records, everything with a “Status” of “4” will be first, “1” will be second, and so on. If there are more status values possible those will be put to the back of the order, as the pre-defined values take precedence over the rest of them.
You can also use text as an ordering value (which is a good thing sometimes), which will look like so:
SELECT * FROM where_ever ORDER BY FIELD(fieldToOrderBy,’Medium’,’Large’,’Small’)
The above will be helpful if you want to show all the Medium items first, Large next, and so on.
To sum up, before you do like I did and spend a lot of time going about things via server side code, re-read the MySQL (or whatever database system is you poison) documentation. You are almost guaranteed to find some useful shortcut to save time, code, and headache.
Cheers