6

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

Is there a way to have SQL order records by analysing the entire number, instead of number by number?

Improve this question
1
  • Are you sure this problem is in any way related to PHP itself? Also, keep in mind that your query is highly vulnerable for SQL injection Commented Aug 21, 2023 at 8:02

4 Answers 4

Reset to default
22

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
)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

That's not a hack, that's a solid strategy! And sometimes it is the best option if you're querying a database you can't (or shouldn't) modify. Another similar idea would be to make some sort of temp table to hold the InstrumentIDs and preferred ordering, then just join to that temp table.
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?

Improve this answer

4 Comments

Good point. Ideally, the instruments would be listed thusly: Flute 1 Flute 2 Flute 3 Alto Flute Bass Flute... Ordering by a score_order field would still be alphanumeric, and that's not quite what I'm looking for.
I've appended some example tables to the question so you can better visualize it. Thanks :)
Can you not make score_order a numeric field, rather than alphanumeric? (I'm assuming score_order is the order the instruments appear in a musical score; have I misinterpreted?)
Problem comes when you need to change this ordering; there are common situations where many many items would have to have their order number changed just to insert.
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)

Improve this answer

2 Comments

So if I ordered by type string, MySQL orders them alphanumerically, but doesn't do so if the column ordered by is of type integer? Interesting... To throw a spanner in the works, however, the numbers need to be integrated into the result set, so all "Flutes" are together, and all "Alto Flutes" are together, irregardless of number.
You're the one setting the priority so you can set it to do the ordering however you like. In your query you're just putting "ORDER BY priority ASC, name ASC"
1

Use the MySQL FIELD() function, like so:

SELECT * FROM `instruments` WHERE `name` LIKE '%$name%'
ORDER BY FIELD( `name`,
    'Piccolo',
    'Flute 2',
    'Flute 7',
    'Flute 10',
    'Alto Flute',
    'Bass Flute'
)

[insert "parameterize your queries" warning here]

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.