Problem sorting Swedish characters Å Ä Ö MySQL

Question

I'm trying to sort a list with asc or desc - depending on what the user choose. In the list I have the Swedish characters Å Ä Ö and it's here the problem shows up. I have the following list:

(First list)
Stängd
Stängd
Öppen
Krävs ej
Krävs ej

(Standing for; Stängd = Closed, Öppen = Opened, Krävs ej = Not required)

The list should be sorted - depending on what the user choose;

Öppen
Stängd
Stängd
Krävs ej
Krävs ej

or

Krävs ej 
Krävs ej
Stängd 
Stängd 
Öppen 

But as it is now the first list is showing up. So the problem is the "Ö" -character. My database and the field that the value is in have the collation utf8_general_ci, so that is'nt the problem. And the character "Ö" is right in both database (looking throu PHPMyAdmin) and output right when printing it out.

My code look like this:

$querystr = "
    SELECT wposts.*
    FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
    WHERE wposts.ID = wpostmeta.post_id
    AND wpostmeta.meta_key = '$sort_by'
    AND wposts.post_type = 'sida'
    AND wposts.post_status = 'publish'
    ORDER BY wpostmeta.meta_value $sort_order";

How can this appear and how does I solve it?

Answer 1

My database and the field that the value is in have the collation utf8_general_ci, so that is'nt the problem

But it is. :) Different collations have different sort orders, and different ways how they interpret umlauts.

utf8_general_ci will sort Ö with O. Try the utf8_swedish_ci instead. That will have the correct sorting order, which (IIRC) is that ÄAÖ go to the end of the alphabet.

For background info, see 9.1.7.8. Examples of the Effect of Collation

Answer 2

The general collation places Ö with O. If you want Ö at the end of the alphabet then you need to use utf8_swedish_ci.