MYSQL – Order By Field

June 6th, 2012 | Comment on this post

Found a really interesting way of ordering a recordset using field values. Most of the time, I need to order results in ascending or descending order. Today, I needed to be able to order the MYSQL recordset by field values. Below is the original query

<?

$query = "SELECT DISTINCT instgroup FROM secondhand DESC:";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)) {
    echo $row['instgroup'] . "
"; } /* Based on my table, the above query will output the following: Baritone Horn Bassoon Clarinet Cornet Euphonium */ ?>

I wanted the field values to be in a different order, such as Clarinet, Baritone Horn, Euphonium, Bassoon, Cornet?
Thankfully, MYSQL has a handy function called ORDER BY FIELD, see code below:

<?

$query = "SELECT DISTINCT instgroup FROM secondhand 
ORDER BY FIELD(instgroup, 
'Clarinet', 
'Baritone Horn', 
'Euphonium', 
'Bassoon', 
'Cornet') DESC";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)) {
    echo $row['instgroup'] . "
"; } /* Based on my table, the above query will output the following: Clarinet Baritone Horn Euphonium Bassoon Cornet */ ?>