Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#1 2022-02-10 12:08:10

helsinkifrostbites
Member
From: Ystad, Sweden
Registered: 2007-11-16
Posts: 44
Website

Swedish collation fail despite utf8_swedish_ci

I’m pulling my hair out with this one. I’ve got a Swedish site oskg.nu with a list of artists in alphabetical order. I can’t get them to sort according to correct Swedish alphabet (åäö at the end), despite having set the collation and charset to utf8_swedish_ci everywhere I could find.

Here’s that page: https://oskg.nu/konstnaerer/

I have a plain < txp:article limit=“200” sort=“Title” / > in my page template.

The whole database, including the Textpattern table and the Title field, has collation set to utf8_swedish_ci

In textpattern/config.php, it says:
$txpcfg[‘dbcharset’] = ‘utf8_swedish_ci’;
$txpcfg[‘table_collation’] = ‘utf8_swedish_ci’;

What the heck am I missing?

Last edited by helsinkifrostbites (2022-02-10 12:22:07)

Offline

#2 2022-02-10 12:45:37

etc
Developer
Registered: 2010-11-11
Posts: 4,662
Website GitHub

Re: Swedish collation fail despite utf8_swedish_ci

Have you tried < txp:article limit="200" sort="BINARY Title" / >? Or using utf8_swedish_cs collation?

Offline

#3 2022-02-10 13:07:32

helsinkifrostbites
Member
From: Ystad, Sweden
Registered: 2007-11-16
Posts: 44
Website

Re: Swedish collation fail despite utf8_swedish_ci

etc wrote #332719:

Have you tried < txp:article limit="200" sort="BINARY Title" / >? Or using utf8_swedish_cs collation?

Tried BINARY it now; no effect.
Where do you suggest I use utf8_swedish_cs? It doesn’t seem to be an available option in phpMyAdmin, and in config.php it had no effect.

Offline

#4 2022-02-12 02:00:46

phiw13
Plugin Author
From: Japan
Registered: 2004-02-27
Posts: 2,697
Website

Re: Swedish collation fail despite utf8_swedish_ci

What is the public-side language set to? (Site language on the Admin > Languages panel). If it is set to something other that Swedish, I think that would affect sort order.

I could be wrong though…


Where is that emoji for a solar powered submarine when you need it ?

Offline

#5 2022-02-13 12:55:14

helsinkifrostbites
Member
From: Ystad, Sweden
Registered: 2007-11-16
Posts: 44
Website

Re: Swedish collation fail despite utf8_swedish_ci

phiw13 wrote #332724:

What is the public-side language set to? (Site language on the Admin > Languages panel). If it is set to something other that Swedish, I think that would affect sort order.

I could be wrong though…

It’s set to Swedish. I don’t get it, what else is there?

Offline

#6 2022-02-13 20:14:05

etc
Developer
Registered: 2010-11-11
Posts: 4,662
Website GitHub

Re: Swedish collation fail despite utf8_swedish_ci

I currently see e.g.

Maurin, Jean Louis
Månsson, Ann-Louise

Is this sort order wrong?

Offline

#7 2022-02-14 12:09:28

helsinkifrostbites
Member
From: Ystad, Sweden
Registered: 2007-11-16
Posts: 44
Website

Re: Swedish collation fail despite utf8_swedish_ci

etc wrote #332732:

I currently see e.g.

Maurin, Jean Louis...

Is this sort order wrong?

Really? I see Månsson, Mattsson, Maurin. Am I seeing different results locally? Am I going mad?

Offline

#8 2022-02-14 12:23:42

helsinkifrostbites
Member
From: Ystad, Sweden
Registered: 2007-11-16
Posts: 44
Website

Re: Swedish collation fail despite utf8_swedish_ci

Ok, I see: when i do sort=“BINARY Title asc”, it sorts “Maurin” before “Månsson”, but “af Rolén” after “Zinnerström” (which is sub-optimal), and still “Åström” before “Bagge”. Completely off-nominal, in other words. Without BINARY, I get correct English order, which doesn’t distinguish between a and ä.

The order I’m after goes a, b, c, d, … x, y, z, å, ä, ö.

Offline

#9 2022-02-14 12:24:07

etc
Developer
Registered: 2010-11-11
Posts: 4,662
Website GitHub

Re: Swedish collation fail despite utf8_swedish_ci

helsinkifrostbites wrote #332739:

I see Månsson, Mattsson, Maurin.

Weirder and weirder, me too now. But I’d swear yesterday the order was correct, also with Börjesson, Maria after Borselius, Ralf. Have you changed something in between?

Edit: ah, ok. Can you try BINARY UPPER(Title) or some variation?

Offline

#10 2022-02-14 13:18:02

helsinkifrostbites
Member
From: Ystad, Sweden
Registered: 2007-11-16
Posts: 44
Website

Re: Swedish collation fail despite utf8_swedish_ci

etc wrote #332741:

Can you try BINARY UPPER(Title) or some variation?

Tried it, didn’t help. I still get Å and A together.

Offline

#11 2022-02-14 13:22:45

helsinkifrostbites
Member
From: Ystad, Sweden
Registered: 2007-11-16
Posts: 44
Website

Re: Swedish collation fail despite utf8_swedish_ci

I even tried < txp:article sort=“Title COLLATE utf8_swedish_ci ASC” / > to no avail. Still sorts Arkrans, Åstrand, Bagge.

Offline

#12 2022-02-14 19:21:35

helsinkifrostbites
Member
From: Ystad, Sweden
Registered: 2007-11-16
Posts: 44
Website

Re: Swedish collation fail despite utf8_swedish_ci

Welp, I’m going with a band-aid solution: custom field called “sort_order”, containing the names same as Title, except adding z’s where needed to fix the order. To make Åstrand come after Zinnerström, i enter “zzÅstrand”. It feels stupid, but it works. Now I just have to update all 117 artist articles, and also teach my client to do it for any future updates.

Thanks for trying to help, etc & phiw13!

Last edited by helsinkifrostbites (2022-02-14 19:22:33)

Offline

Board footer

Powered by FluxBB