Strange sorting problem in PHP, mysql with utf8 da

Question

I have a very strange problem with one of my db tables. I have various areas categorized in zones (Zone A, Zone B etc) and when I run a query to display them in a dropdown menu i get

• Zone A - Area K
• Zone A - Area L
• Zone A - Area M
• Zone A - Area N
• Zone A - Area O
• Zone A - Area P
• Zone A - Area A
• Zone A - Area B
• Zone A - Area C
• Zone A - Area D
• ...
• Zone A - Area G
• Zone B - Area K
• Zone B - Area L
• Zone B - Area M
• Zone B - Area N
• Zone B - Area O
• Zone B - Area P
• Zone B - Area A
• Zone B - Area B
• Zone B - Area C
• Zone B - Area D

For some strange reason the areas are being displayed from K->Z and then starts from A.

My table's structure is as follows: CREATE TABLE areas ( id int(11) NOT NULL AUTO_INCREMENT, zone varchar(20) DEFAULT NULL, area varchar(100) NOT NULL, distance float(9,2) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The data in my table is in Greek, I made the above example only so you could understand and see the problem. To view the actual data (in Greek) you can visit http://www.emanaviko.gr/lists/suburbs.php

Answer 1

I've imported your data into a table and it seems to sort different from what you get. What is your default collation ? Mine is utf8_general_ci.

Note that even your western-looking letters are, in fact, not western letters. For instance, your K is in fact the unicode character "GREEK CAPITAL LETTER KAPPA".

mysql> show create table foo;

+-------+----------------------------------------------------------------------------------+
| Table | Create Table                                                                     |
+-------+----------------------------------------------------------------------------------+
| foo   | CREATE TABLE `foo` (
  `z` text,
  `a` text
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------+

mysql> select * from foo order by z,a collate utf8_general_ci;
+-------------+-------------------------------------------------+
| z           | a                                               |
+-------------+-------------------------------------------------+
| ΖΩΝΗ Α | Αγία Βαρβάρα                         |
| ΖΩΝΗ Α | Αγία Παρασκευή                     |
| ΖΩΝΗ Α | Αγιοι Ανάργυροι                   |
| ΖΩΝΗ Α | Αγιος Στέφανος                     |
| ΖΩΝΗ Α | Αιγάλεω                                  |
| ΖΩΝΗ Α | Ανθούσα                                  |
| ΖΩΝΗ Α | Ανοιξη                                    |
| ΖΩΝΗ Α | Ανω Λιόσια                             |
| ΖΩΝΗ Α | Αχαρνές (Μενίδι)                   |
| ΖΩΝΗ Α | Βριλήσσια                              |
| ΖΩΝΗ Α | Γαλάτσι                                  |
| ΖΩΝΗ Α | Γέρακας                                  |
| ΖΩΝΗ Α | Γλυκά Νερά                             |
| ΖΩΝΗ Α | Διόνυσος                                |
| ΖΩΝΗ Α | Δροσιά                                    |
| ΖΩΝΗ Α | Εκάλη                                      |
| ΖΩΝΗ Α | Ζεφύρι                                    |
| ΖΩΝΗ Α | Ηράκλειο                                |
| ΖΩΝΗ Α | Θρακομακεδόνες                    |
| ΖΩΝΗ Α | Ιλιον                                      |
| ΖΩΝΗ Α | Κηφισιά                                  |
| ΖΩΝΗ Α | Κρυονέρι                                |
| ΖΩΝΗ Α | Λυκόβρυση                              |
| ΖΩΝΗ Α | Μαρούσι                                  |
| ΖΩΝΗ Α | Μελίσσια                                |
| ΖΩΝΗ Α | Νέα Ερυθραία                         |
| ΖΩΝΗ Α | Παλλήνη                                  |
| ΖΩΝΗ Α | Παπάγου                                  |
| ΖΩΝΗ Α | Πεντέλη                                  |
| ΖΩΝΗ Α | Περιστέρι                              |
| ΖΩΝΗ Α | Πετρούπολη                            |
| ΖΩΝΗ Α | Πεύκη                                      |
| ΖΩΝΗ Α | Ροδόπολη                                |
| ΖΩΝΗ Α | Σταμάτα                                  |
| ΖΩΝΗ Α | Φιλοθέη                                  |
| ΖΩΝΗ Α | Χαϊδάρι                                  |
| ΖΩΝΗ Α | Χαλάνδρι                                |
| ΖΩΝΗ Α | Χαλκηδόνα                              |
| ΖΩΝΗ Α | Χολαργός                                |
| ΖΩΝΗ Α | Ψυχικό                                    |
| ΖΩΝΗ Β | Αγία Σοφία                             |
| ΖΩΝΗ Β | Αγιος Βασίλειος                   |
| ΖΩΝΗ Β | Αγιος Δημήτριος(Μπραχάμι) |
| ΖΩΝΗ Β | Αλιμος                                    |
| ΖΩΝΗ Β | Ανω Γλυφάδα                           |
| ΖΩΝΗ Β | Αργυρούπολη                          |
| ΖΩΝΗ Β | Βάρη                                        |
| ΖΩΝΗ Β | Βάρκιζα                                  |
| ΖΩΝΗ Β | Βούλα                                      |
| ΖΩΝΗ Β | Βουλιαγμένη                          |
| ΖΩΝΗ Β | Βύρωνας                                  |
| ΖΩΝΗ Β | Γλυφάδα                                  |
| ΖΩΝΗ Β | Δάφνη                                      |
| ΖΩΝΗ Β | Ελληνικό                                |
| ΖΩΝΗ Β | Ζωγράφου                                |
| ΖΩΝΗ Β | Ηλιούπολη                              |
| ΖΩΝΗ Β | Καβούρι                                  |
| ΖΩΝΗ Β | Καισαριανή                            |
| ΖΩΝΗ Β | Καλλιθέα                                |
| ΖΩΝΗ Β | Καλλίπολη                              |
| ΖΩΝΗ Β | Καμίνια                                  |
| ΖΩΝΗ Β | Καστέλα                                  |
| ΖΩΝΗ Β | Νέα Σμύρνη                             |
| ΖΩΝΗ Β | Παλαιό Φάληρο                       |
| ΖΩΝΗ Β | Υμηττός                                  |
+-------------+-------------------------------------------------+
65 rows in set (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from foo order by z,a collate utf8_general_ci;
+-------------+-------------------------------------------------+
| z           | a                                               |
+-------------+-------------------------------------------------+
| ΖΩΝΗ Α | Αγία Βαρβάρα                         |
| ΖΩΝΗ Α | Αγία Παρασκευή                     |
| ΖΩΝΗ Α | Αγιοι Ανάργυροι                   |
| ΖΩΝΗ Α | Αγιος Στέφανος                     |
| ΖΩΝΗ Α | Αιγάλεω                                  |
| ΖΩΝΗ Α | Ανθούσα                                  |
| ΖΩΝΗ Α | Ανοιξη                                    |
| ΖΩΝΗ Α | Ανω Λιόσια                             |
| ΖΩΝΗ Α | Αχαρνές (Μενίδι)                   |
| ΖΩΝΗ Α | Βριλήσσια                              |
| ΖΩΝΗ Α | Γαλάτσι                                  |
| ΖΩΝΗ Α | Γέρακας                                  |
| ΖΩΝΗ Α | Γλυκά Νερά                             |
| ΖΩΝΗ Α | Διόνυσος                                |
| ΖΩΝΗ Α | Δροσιά                                    |
| ΖΩΝΗ Α | Εκάλη                                      |
| ΖΩΝΗ Α | Ζεφύρι                                    |
| ΖΩΝΗ Α | Ηράκλειο                                |
| ΖΩΝΗ Α | Θρακομακεδόνες                    |
| ΖΩΝΗ Α | Ιλιον                                      |
| ΖΩΝΗ Α | Κηφισιά                                  |
| ΖΩΝΗ Α | Κρυονέρι                                |
| ΖΩΝΗ Α | Λυκόβρυση                              |
| ΖΩΝΗ Α | Μαρούσι                                  |
| ΖΩΝΗ Α | Μελίσσια                                |
| ΖΩΝΗ Α | Νέα Ερυθραία                         |
| ΖΩΝΗ Α | Παλλήνη                                  |
| ΖΩΝΗ Α | Παπάγου                                  |
| ΖΩΝΗ Α | Πεντέλη                                  |
| ΖΩΝΗ Α | Περιστέρι                              |
| ΖΩΝΗ Α | Πετρούπολη                            |
| ΖΩΝΗ Α | Πεύκη                                      |
| ΖΩΝΗ Α | Ροδόπολη                                |
| ΖΩΝΗ Α | Σταμάτα                                  |
| ΖΩΝΗ Α | Φιλοθέη                                  |
| ΖΩΝΗ Α | Χαϊδάρι                                  |
| ΖΩΝΗ Α | Χαλάνδρι                                |
| ΖΩΝΗ Α | Χαλκηδόνα                              |
| ΖΩΝΗ Α | Χολαργός                                |
| ΖΩΝΗ Α | Ψυχικό                                    |
| ΖΩΝΗ Β | Αγία Σοφία                             |
| ΖΩΝΗ Β | Αγιος Βασίλειος                   |
| ΖΩΝΗ Β | Αγιος Δημήτριος(Μπραχάμι) |
| ΖΩΝΗ Β | Αλιμος                                    |
| ΖΩΝΗ Β | Ανω Γλυφάδα                           |
| ΖΩΝΗ Β | Αργυρούπολη                          |
| ΖΩΝΗ Β | Βάρη                                        |
| ΖΩΝΗ Β | Βάρκιζα                                  |
| ΖΩΝΗ Β | Βούλα                                      |
| ΖΩΝΗ Β | Βουλιαγμένη                          |
| ΖΩΝΗ Β | Βύρωνας                                  |
| ΖΩΝΗ Β | Γλυφάδα                                  |
| ΖΩΝΗ Β | Δάφνη                                      |
| ΖΩΝΗ Β | Ελληνικό                                |
| ΖΩΝΗ Β | Ζωγράφου                                |
| ΖΩΝΗ Β | Ηλιούπολη                              |
| ΖΩΝΗ Β | Καβούρι                                  |
| ΖΩΝΗ Β | Καισαριανή                            |
| ΖΩΝΗ Β | Καλλιθέα                                |
| ΖΩΝΗ Β | Καλλίπολη                              |
| ΖΩΝΗ Β | Καμίνια                                  |
| ΖΩΝΗ Β | Καστέλα                                  |
| ΖΩΝΗ Β | Νέα Σμύρνη                             |
| ΖΩΝΗ Β | Παλαιό Φάληρο                       |
| ΖΩΝΗ Β | Υμηττός                                  |
+-------------+-------------------------------------------------+

Update :

OK. I imported your dump file.

Your problem is that the tables contain the wrong data. It is in fact double-encoded utf8.

Look into the tables using phpmyadmin : you should see garbage instead of text.

You probably have a bug in your PHP code, that you forgot to issue a "SET NAMES utf8" or a mysql_set_charset("utf8"). And you had the same bug when inserting.

Therefore, when you issue

INSERT 'Αγία Παρασκευή'

PHP sends utf-8 encoded data to MySQL. But MySQL believes it is latin1. So, it converts it again to utf8, and your table contains garbage.

When you SELECT, mysql converts utf8 back to latin1, but this actually gives you the utf8 you sent originally, so it displays correctly in your application.

But the contents of the tables are garbage, and sort in garbage order ;)

First you need to fix that bug in your code.

Then you need to export those tables (the file you gave works fine), drop them, and re-import them using the correct charset (add a SET NAMES utf8 at the top of the file).

Update 2 :

If you want to know if the data isn't garbage, there are simple tricks :

SELECT upper(yourcolumn) (or lower()...) 
SELECT char_length(yourcolumn)

If it works (ie, you get the right uppercase or lowercase text and the right length in characters, then it means the data is good and mysql understands it.

Common trick to check if your mysql connection is properly configured is to issue (in your php code) this query :

SELECT char_length('é'), octet_length('é'), upper('é')
+-------------------+--------------------+-------------+
| char_length('é') | octet_length('é') | upper('é') |
+-------------------+--------------------+-------------+
|                 1 |                  2 | É          |
+-------------------+--------------------+-------------+

Of course you can do

SHOW VARIABLES LIKE '%character%';

I use phpmyadmin and the mysql console.

Answer 2

Add ORDER BY clause to your query:

SELECT  *
FROM    areas
ORDER BY
        zone, area