Sorting SQL row output per an arbitrary order?

Question

So, in my database, I store musical instrument names (and various other attributes). Let's say id is the primary key, and name is a unique key.

In a PHP script, I select items by their instrument class, like so:

$name = mysql_real_escape_string($_POST['name']);
$row = mysql_fetch_row(mysql_query("SELECT * FROM `instruments` WHERE name LIKE '%$name%' ORDER BY id"));

The result table:

id    name
1     "Flute 2"
2     "Bass Flute"
3     "Flute 10"
4     "Flute 7"

This allows me to select the entire family of instruments, like the "Soprano Saxophone", "Alto Saxophone", etc, just by querying "Saxophone".

In that particular example, the results are orered by their id (which you can assume is auto_incremented). More ideal would be ordering by alphabetical order, yes?

id    name
2     "Bass Flute"
3     "Flute 10"
1     "Flute 2"
4     "Flute 7"

That works fine, but being musicians, they like to screw around with the DB admin, and don't take kindly to having "Bass Flutes" listed above "Flute" in the Flute results, and really don't take kindly to having "Flute 10" listed before "Flute 2".

So, seeing as there's no ORDER BY score_order (that would be too easy, wouldn't it...), how could I introduce some sort of ordering system to display instruments properly?

Upon brainstorming, I came upon one solution: I could add one more column of type integer, and "rank" the instruments based on their importance (that is, Piccolos would be "1", Flutes "2", etc):

... nts` WHERE name LIKE '%$name%' ORDER BY rank, name"));

id    name           rank
3     "Flute 10"     2
1     "Flute 2"      2
4     "Flute 7"      2
2     "Bass Flute"   5

However, this doesn't account for the fact that "Flute 10" comes before "Flute 2", alphanumerically.

The ideal result table (ordered by rank, and then by name):

id    name           rank
6     "Piccolo"      1
1     "Flute 2"      2
4     "Flute 7"      2
3     "Flute 10"     2
5     "Alto Flute"   4
2     "Bass Flute"   5

So, my questions:

1. Is this a feasible solution, and is it worth the effort to implement?
2. Is there a way to have SQL order records by analysing the entire number, instead of number by number?

Thanks!

Answer 1

What I would do is put a "priority" column in your table. Then order it by most important (eg base instruments would be 0: Flute, Saxophone, etc, Modifications would be 1: Bass Flute, Alto Flute, etc, and Numerics' priority would be their number + 100 or something to put them at the end but still in numeric order).

The best way to sort numbers (and then alphabetically in cases of the same priority) numerically is by using numbers.

Edit: So, the above would give you something like this (the priority is in parentheis):
Flute (0)
Bass Flute (1)
Tenor Flute (1)
Flute 1 (101)
Flute 2 (102)
Flute 10 (110)

Answer 2

It's not totally clear to me what your ideal sort order would be, but you should probably just make it an extra column in your database table. You say ORDER BY score_order would be too easy, but why not add an explicit score_order field and order by that?

Answer 3

You can create a different order doing something like this. It's a hack.

select * from table
order by (
     case name
     when 'Health' then 0 
     when 'Flute 10' then 1
     when 'Freeze' then 2
     when 'Bass Flute' then 3
     end
)

And it's probably a better idea to use the id column.

select * from table
order by (
    case id
    when 3 then 0 
    when 1 then 1
    when 4 then 2
    when 2 then 3
    end
)