MySQL by default will make the ORDER BY option in queries case sensitive. This means that rows with the same starting letters (but different case) may be ordered non-alphabetically. There are three solutions to this. One is to force all database entries to be forced to upper-case or lower-case when inserting. Another is to use the collate option. The other solution is to change the query itself.
Option 2 (Thanks to eremit)
mysql_query(“SELECT * FROM names ORDER BY name COLLATE ‘latin1_general_ci’);
See: MySQL Reference – Case Sensitivity in String Searches and MySQL Reference – Character Set Support
Option 3:
The MySQL query for case insensitive ORDER BY is to add the LOWER() to your field name. For instance:
mysql_query(“SELECT * FROM names ORDER BY lastname”);
will be: mysql_query(“SELECT * FROM names ORDER BY LOWER(lastname)“);
Pingback: Daynah
Generally that’s worth a try, but as far as I remember accents might be still a problem.
Another solution to get an case-insensitive ORDER BY is:
mysql_query(“SELECT * FROM names ORDER BY name COLLATE ‘latin1_general_ci’);
See: MySQL Reference – Case Sensitivity in String Searches and MySQL Reference – Character Set Support
Thanks for bringing up the point about the accents. The COLLATE ‘latin1_general_ci’ is a much better solution. I’ll update the post with your helpful tip.
very nice blog 🙂
Hi,
Thank you for this article. I used the variant with LOWER(`name`) and works very fine :).