Swedish collation fail despite utf8_swedish_ci

helsinkifrostbites · 2022-02-10 12:08:10

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)

etc · 2022-02-10 12:45:37

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

helsinkifrostbites · 2022-02-10 13:07:32

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.

phiw13 · 2022-02-12 02:00:46

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…

helsinkifrostbites · 2022-02-13 12:55:14

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?

etc · 2022-02-13 20:14:05

I currently see e.g.

Maurin, Jean Louis
Månsson, Ann-Louise

Is this sort order wrong?

helsinkifrostbites · 2022-02-14 12:09:28

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?

helsinkifrostbites · 2022-02-14 12:23:42

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, å, ä, ö.

etc · 2022-02-14 12:24:07

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 Börjesson, Maria after Borselius, Ralf. Have you changed something in between?

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

helsinkifrostbites · 2022-02-14 13:18:02

etc wrote #332741:

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

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

helsinkifrostbites · 2022-02-14 13:22:45

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

helsinkifrostbites · 2022-02-14 19:21:35

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)

Textpattern CMS

Textpattern CMS support forum

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