My Blog
Blog post #030
Coquitlam BC, Canada
Posted 2 weeks ago
PHP / MySQL ORDER BY clause
MySQL does not support
alphanumeric natural sorting
If you have set up a website database with MySQL or MariaDB you may have come across the problem with the MySQL ORDER BY clause not sorting some data as you would expect (example: "item 1", "item 2", "item 10" instead of "item 1", "item 10", "item 2"). MySQL sorts data using a lexicographical (character-by-character) sorting order, and since 1 is lower than 2, the number 10 will come before 2.
In the example below, the data from DBcolumn1 is sorted by using the ORDER BY clause, but due to the character-by-character sorting, the output highlighted in orange is in the wrong order. This can be solved by adding padded leading zeros to the numbers (example: "item 0001", "item 0002", "item 0010").
| DBcolumn1 |
|---|
| Drop-in X4 ND 6 Stop filter |
| Drop-in X4 ND 3 Stop filter |
| EF 24mm f1.4L II USM lens |
| Drop-in X4 Clear filter |
| EF 100mm f2 USM lens |
| Drop-in X4 Autumn & Night Sky filter |
| Drop-in X4 ND 10 Stop filter |
Drop-in X4 Autumn & Night Sky filter
Drop-in X4 Clear filter
Drop-in X4 ND 10 Stop filter
Drop-in X4 ND 3 Stop filter
Drop-in X4 ND 6 Stop filter
EF 100mm f2 USM lens
EF 24mm f1.4L II USM lens
Alphanumeric natural sorting solution
using padded leading zeros (0010) with PHP
MySQL does not natively support "natural sorting" (also known as alphanumeric or human-friendly sorting), but you can use PHP to pad the numbers in a string from a database column with leading zeros and UPDATE that data to a second database column using the PHP preg_replace_callback function.
This will require two MySQL query while loops. The first loop to UPDATE DBcolumn2 with the padded leading zeros, and the second loop outputs the results.
PHP preg_replace_callback function
to UPDATE DBcolumn2
The PHP preg_replace_callback code below is implemented inside a PHP / MySQL database query while loop. During each loop, padded leading zeros are added to the numbers in the string from DBcolumn1 and updated to DBcolumn2 using the MySQL UPDATE statement.
Final results
MySQL will then sort the data from DBcolumn2 using the ORDER BY clause and display the data from DBcolumn1. New data can be inserted or edited into DBcolumn1 as usual, and DBcolumn2 is updated automatically every time the webpage is loaded.
| DBcolumn1 | DBcolumn2 |
|---|---|
| Drop-in X4 ND 6 Stop filter | Drop-in X0004 ND 0006 Stop filter |
| Drop-in X4 ND 3 Stop filter | Drop-in X0004 ND 0003 Stop filter |
| EF 24mm f1.4L II USM lens | EF 0024mm f0001.0004L II USM lens |
| Drop-in X4 Clear filter | Drop-in X0004 Clear filter |
| EF 100mm f2 USM lens | EF 0100mm f0002 USM lens |
| Drop-in X4 Autumn & Night Sky filter | Drop-in X0004 Autumn & Night Sky filter |
| Drop-in X4 ND 10 Stop filter | Drop-in X0004 ND 0010 Stop filter |
Drop-in X4 Autumn & Night Sky filter
Drop-in X4 Clear filter
Drop-in X4 ND 3 Stop filter
Drop-in X4 ND 6 Stop filter
Drop-in X4 ND 10 Stop filter
EF 24mm f1.4L II USM lens
EF 100mm f2 USM lens
Thanks for reading. I hope you found this blog post helpful.