0

In MySQL I have a table with zipcodes and cities. In the database fx. Copenhagen (København) i shown as København, but written in PHP it is correctly shown as København. The table is utf8_general_ci.

When I want to get the entire list of citites, the sorting goes wrong.

København is comming before Kerteminde and Ølgod is before Bogense and that is wrong.

I have tried with ORDER BY city COLLATE 'utf8_danish_ci' asc in my SQL, but that doesn't work.

How can I sort the cities correctly?

Jeppe Donslund
  • 469
  • 9
  • 25
  • Your `COLLATE` statement/settings will only work if your data is actually UTF-8. If your text is ISO-8859-1 or cp1252 then it will be sorted incorrectly. `utf8_danish_ci` stands for "_assume_ my data is UTF8, I want you to sort it danish-style [instead of by ordinal/binary value] and make it case-insensitive" – Sammitch Feb 25 '23 at 23:51

2 Answers2

0

You should set the mysql/mariadb connection to UFT-8. This code should be placed directly after connecting to your database.

mysql_set_charset("utf8");

or

$mysqli->set_charset("utf8");
SelVazi
  • 10,028
  • 2
  • 13
  • 29
-1

If the table is loaded with the correct encoding, are you sorting it before or after the encoding?

One solution might otherwise be to sort it with another encoding, and then use the desired one to render the final product, although a makeshift one.

Simon Tuomi
  • 15
  • 5