Yesterday i had a somewhat new (for me ) and different kind of requirement while displaying MySQL result on a page. I had a category menu in which masters and slaves were sorted alphabetically and in ascending order. Everything was going fine until client put a “View All” link in the menu tree and wanted to show that under each master and at the top of all slaves keeping other slaves intact i.e. sorted alphabetically.
I really wanted to use my own intellect to make it work and i was succeeded in it too. Here is the line which i discovered :) .
SELECT * FROM `$table_name` WHERE parent_id = '$parent_id' ORDER BY `text`='View All' DESC, `text` ASC
The above code worked and worked great!
Now when i had patted my back enough, decided to Google for the similar and possibly more wise and precise solution for this thing to happen (which i had already assumed to be there and it was). This magic word FIELD is a extension to ORDER BY clause and exists in the form of a MySQL function. The query line is:
SELECT * FROM `$table_name` WHERE parent_id = '$parent_id' ORDER BY FIELD( `text`,'View All') DESC, `text` ASC
So if you decide to place ‘View None’ next right under the ‘View All’ and above other slaves sorted alphabetically just place View None’ to make the query look like:
SELECT * FROM `$table_name` WHERE parent_id = '$parent_id' ORDER BY FIELD( `text`,'View All', 'View None') DESC, `text` ASC
I hope this be a nice solution for sorting records by a custom field / value helps many.