In MySQL it is easy to sort rows by using the ORDER BY clause on the column name. Recently on a client request the sort had to be done using the column values rather than the name. Since I had never come across sorting using values, a couple of minutes of searching was in order; You can do the ordering by using the ‘field’ function like in the below example.
The first and second parameters for the field function are the column name and value respectively. If you want more than 1 value to sort by you need to add the remaining afer the second parameter like this.
...field( column_name, value1, value2, value3...)
...ORDER BY field(status, 'For Lease', 'Rent', 'Purchase') desc
Example sorting using the column names.
SELECT * FROM property_master pm,prop_details_master pdm WHERE pdm.property_id = pm.property_id AND pm.active = 1 ORDER BY status desc
Example sorting using column values.
select * FROM property_master pm,prop_details_master pdm WHERE pdm.property_id = pm.property_id AND pm.active = 1 ORDER BY field(status, 'For Lease') desc
This site is a digital habitat of Sameer, a freelance web developer working in PHP & MySQL.More