# PHP and MySQL Natural Sort

## 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 | -----------------------------------------```

## 2 thoughts on “PHP and MySQL Natural Sort”

1. tank for this post

2. Matteo says:

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.