Use Case
Let’s say we have an array of data represented by some text followed by a number. Just like the movies from a movie series like “Mission Impossible” or “Pirates of the Carribean”. We know that they are often followed by the consecutive number of the episode.
Mission: Impossible 1 Mission: Impossible 2 Mission: Impossible 3 ... |
Since we have no more than three or four episodes we can easily sort the array if it’s not sorted initially.
$a = array('Mission: Impossible 2', 'Mission: Impossible 3', 'Mission: Impossible 1'); sort($a); // Mission: Impossible 1 // Mission: Impossible 2 // Mission: Impossible 3 print_r($a); |
However in some cases we can have more than 10 episodes. Then we can meet a problem while sorting the array above.
$a = array('Episode 1', 'Episode 2', 'Episode 11', 'Episode 112'); sort($a); // Episode 1 // Episode 11 // Episode 112 // Episode 2 print_r($a); |
Now because this is by default an alphabetical sort order we get an array that isn’t sorted to our human undestanding.
The question is how to overcome this problem?
PHP
First the thing we actually need is called “natural sort”, so PHP (with its full of handful functions library) takes care for us with natsort.
$a = array('Episode 1', 'Episode 2', 'Episode 11', 'Episode 112'); natsort($a); // Episode 1 // Episode 2 // Episode 11 // Episode 112 print_r($a); |
Now the array is sorted accordingly.
MySQL
MySQL in the other hand appears to be more hostile to natural sorting. We can just have ORDER BY with some keyword in order to sort a column using natural sort.
Given the table data:
my_table ----------------------------------------- | id | name | ----------------------------------------- | 1 | Episode 2 | | 2 | Episode 1 | | 3 | Episode 112 | | 4 | Episode 11 | ----------------------------------------- |
SELECT * FROM my_table ORDER BY name; |
The query above will return the table in an alphabetical order.
my_table ----------------------------------------- | id | name | ----------------------------------------- | 2 | Episode 1 | | 4 | Episode 11 | | 3 | Episode 112 | | 1 | Episode 2 | ----------------------------------------- |
However there are some “hacks”. Here’s one of them.
SELECT * FROM my_table ORDER BY LENGTH(name), name; |
Now the column is sorted correctly.
my_table ----------------------------------------- | id | name | ----------------------------------------- | 2 | Episode 1 | | 1 | Episode 2 | | 4 | Episode 11 | | 3 | Episode 112 | ----------------------------------------- |
tank for this post
You should have said that this works only if the column “name” respects the same format. If we insert “Story 1” as a new column value, this would be put before “Episode 1”, cause the first is the shortest, breaking natural sort.