CODEWITHSUNDEEP
mysqlsortingcharacter
Fredrik
Fredrik

Reputation: 635

Problem sorting Swedish characters Å Ä Ö MySQL

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?

Upvotes: 11

Views: 17066

Answers (3)

Mad Marvin
Mad Marvin

Reputation: 3449

If you want to convert existing tables to a new collation, just use the following:

alter table [tableName] convert to character set utf8 collate utf8_swedish_ci;

Upvotes: 4

Pekka
Pekka

Reputation: 449485

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

Upvotes: 26

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798746

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

Upvotes: 7

Related Questions