[PHP/MySQL] Sorteren op cyrillisch alfabet

[Verwijderd]

Ik heb een tweetalige site gemaakt (Russisch/Nederlands), waarbij tussen de talen is te wisselen door op het vlaggetje te drukken. Geen probleem allemaal, behalve het feit dat de data in de database is opgeslagen in UTF-8 formaat. Als ik nu probeer de data te sorteren, gaat dit prima, zolang hij op Nederlands staat, maar als ik hem op Russisch zet, slaat de volgorde gewoon helemaal nergens meer op. De query betreft de volgende:

code:

$res2 = mysql_query("SELECT CategoryID, Category, Notes_PlainText, 
  SUBSTRING(Notes_PlainText, INSTR(Notes_PlainText, '$lang: ')+LENGTH('$lang: '), 4) AS vert
  FROM Category WHERE CategoryID IN (".join(",", array_keys($catids)).")
  ORDER BY vert COLLATE utf8_general_ci");

Ik heb al meerdere dingen geprobeerd in te vullen achter dat "collate", wat wel effect heeft, maar niet het gewenste... Ik begin zo langzamerhand het idee te krijgen dat het niet mogelijk is op cyrillisch alfabet te sorteren als de database in UTF-8 staat. Heeft iemand desondanks een gouden tip voor mij?

[RobIII]

Ik ben niet bekend met Russisch, maar "maar als ik hem op Russisch zet, slaat de volgorde gewoon helemaal nergens meer op" baseer je op je kennis van Russisch of...? Misschien is het voor Russische begrippen juist wel goed gesorteerd? Heb je wat voorbeeld output?

edit: Euh... en wat doet dit:

SQL:

SUBSTRING(Notes_PlainText, INSTR(Notes_PlainText, '$lang: ')+LENGTH('$lang: '), 4)

En waarom sorteer je daar op? Je hebt toch niet twee talen in 1 veld zitten?

[ Voor 29% gewijzigd door RobIII op 30-10-2008 13:31 ]

[Verwijderd]

RobIII schreef op donderdag 30 oktober 2008 @ 13:29:
Ik ben niet bekend met Russisch, maar "maar als ik hem op Russisch zet, slaat de volgorde gewoon helemaal nergens meer op" baseer je op je kennis van Russisch of...? Misschien is het voor Russische begrippen juist wel goed gesorteerd? Heb je wat voorbeeld output?

Ik studeer russisch, en kan je vertellen dat de volgorde kant nog wal raakt De volgorde die je krijgt met de query die ik net postte is:

Общая информация
Нидерландистика
Россиеведение
Славистика
Справочные издания
Журналы, газеты
Литература о Нидерландах

terwijl dat zou moeten zijn:

Журналы, газеты
Литература о Нидерландах
Нидерландистика
Общая информация
Россиеведение
Славистика
Справочные издания

[Verwijderd]

RobIII schreef op donderdag 30 oktober 2008 @ 13:29:
edit: Euh... en wat doet dit:

SQL:

SUBSTRING(Notes_PlainText, INSTR(Notes_PlainText, '$lang: ')+LENGTH('$lang: '), 4)

En waarom sorteer je daar op? Je hebt toch niet twee talen in 1 veld zitten?

Jawel, maar dat komt omdat ik geen andere keus heb. Database is output van een ander programma (BookCAT), en de enige manier om die 2 talen er dynamisch in te zetten op een manier waarop de klant dat ook makkelijk zelf kan wijzigen, is door die talen in het programma in de "notes" te zetten...

In dat veld staat bijvoorbeeld:

Dutch: Neerlandistiek
Russian: Нидерландистика

Ik begrijp dat het niet optimaal is, maar ik wist geen andere oplossing. Kan de database niet aanpassen in dat programma namelijk.

[ Voor 20% gewijzigd door Verwijderd op 30-10-2008 14:15 ]

[RobIII]

En je hebt al eens gekeken wat die SUBSTRING(...) teruggeeft?

[Johnny]

Een ranzige oplossing zou zijn om de Cyrillische tekst te om te zetten naar het latijnse karakter dat op dezelfde postitie in het alfabet staat, dat opslaan in een sorteerkolom en die gebruiken om te sorteren.

Maar aangezien je de database niet kan wijzigen is dat ook niet mogelijk, wat ik in zo'n situatie zou doen is een tweede database aanmaken die je wel zelf kan indelen en bewerken op een zinnige manier en via een script ze met elkaar laten synchroniseren, maar dat is ook nogal drastisch.

[ Voor 41% gewijzigd door Johnny op 30-10-2008 13:48 ]

[Maasluip]

Heb je al eens geprobeerd te sorteren met utf8_unicode_ci?
En wat gebeurt er als je de cyrillische tekst gewoon vooraan in een veld zet en zonder opties sorteert?

Oh ja, en welke versie van MySQL heb je? Lager dan 4.1 is een no-no voor UTF-8, ergens vanaf een subversie van 4.1 wordt het beter, maar er zijn nog altijd flinke bugs.
Misschien is het wel een bugreport waard.

Zie UTF-8 survival ergens vanaf pagina 43 voor hints en tips en settings.

[edit]
WTF, ik zie nu dat je dat lijstje in cyrillisch gepost hebt. Ik lees meestal in Lynx, en ik zie dit:

RobIII schreef op donderdag 30 oktober 2008 @ 13:29:
Ik ben niet bekend met Russisch, maar "maar als ik hem op Russisch zet, slaat de
volgorde gewoon helemaal nergens meer op" baseer je op je kennis van Russisch
of...? Misschien is het voor Russische begrippen juist wel goed gesorteerd? Heb je
wat voorbeeld output?

Ik studeer russisch, en kan je vertellen dat de volgorde kant nog wal raakt ;) De
volgorde die je krijgt met de query die ik net postte is:
Obschaya informaciya
Niderlandistika
Rossievedenie
Slavistika
Spravochnye izdaniya
ZHurnaly, gazety
Literatura o Niderlandah
terwijl dat zou moeten zijn:
ZHurnaly, gazety
Literatura o Niderlandah
Niderlandistika
Obschaya informaciya
Rossievedenie
Slavistika
Spravochnye izdaniya

UTF-8 vertaling FTW!

[ Voor 99% gewijzigd door Maasluip op 30-10-2008 14:18 ]

[Cartman!]

Zit je db nu niet gewoon te sorteren op de getallen van je entities ipv. de daadwerkelijke karakters? Waarom zet je uberhaupt alle content als entities in je db als ie utf8 is ?

[Maasluip]

Volgens mij zijn de entities van UTF-8 in ISO 8859-5 goed gesorteerd, dus een sortering op karakter is een sortering op entityvalue.
Zie ISO 8859-5 codepage.

[ Voor 17% gewijzigd door Maasluip op 30-10-2008 14:05 ]

[Verwijderd]

Cartman! schreef op donderdag 30 oktober 2008 @ 14:02:
Zit je db nu niet gewoon te sorteren op de getallen van je entities ipv. de daadwerkelijke karakters? Waarom zet je uberhaupt alle content als entities in je db als ie utf8 is ?

Die entities komen door tweakers, weet niet waarom ie het zo doet. In de database is het gewoon UTF-8

[.oisyn]

Verwijderd schreef op donderdag 30 oktober 2008 @ 13:34:
In dat veld staat bijvoorbeeld:

Dutch: Neerlandistiek
Russian: Нидерландистика

Gebruik de [pre]...[/pre] tag voor dat soort dingen, de code tag bugt namelijk

[Verwijderd]

MySQL versie:

Server version: 5.1.24-rc-log FreeBSD port: mysql-server-5.1.24_1

Dat zou in elk geval goed genoeg moeten zijn dan, toch?

[Maasluip]

Ja, of er zitten nog altijd bugs in natuurlijk.
Heb je al eens een tabel met "gewone" UTF-8 data proberen te sorteren?

En nog een interessant linkje. Hoe heb je je tabel aangemaakt? Dit zou de juiste manier moeten zijn:

CREATE TABLE document (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  unicodeText VARCHAR(45) NOT NULL
)
CHARACTER SET utf8 COLLATE utf8_general_ci;

[Verwijderd]

Maasluip schreef op donderdag 30 oktober 2008 @ 14:26:
Ja, of er zitten nog altijd bugs in natuurlijk.
Heb je al eens een tabel met "gewone" UTF-8 data proberen te sorteren?

En nog een interessant linkje. Hoe heb je je tabel aangemaakt? Dit zou de juiste manier moeten zijn:

CREATE TABLE document (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  unicodeText VARCHAR(45) NOT NULL
)
CHARACTER SET utf8 COLLATE utf8_general_ci;

Hij wordt zo aangemaakt:

code:

DROP TABLE IF EXISTS `Category`;
CREATE TABLE `Category` (
  `CategoryID` INT NOT NULL AUTO_INCREMENT,
  `Marked` BIT DEFAULT 0,
  `Category` VARCHAR(50) CHARACTER SET utf8 ,
  `SortBy` VARCHAR(52) CHARACTER SET utf8 ,
  `Notes` LONGBLOB,
  `Notes_PlainText` LONGTEXT CHARACTER SET utf8 ,
  INDEX `Category` (`Category`),
  PRIMARY KEY (`CategoryID`),
  INDEX `SortBy` (`SortBy`)
) ENGINE=InnoDB;

Ook hier heb ik helaas geen invloed op, aangezien dit gewoon letterlijk de output is van een programma dat MSAccess omzet naar MySQL...

[Verwijderd]

Zit me nu af te vragen of het misschien helpt als ik de database converteer naar koi8r met het commando

code:

alter database nispb default character set koi8r;

Ben alleen bang dat ik dan de hele boel mol...

Als het wel werkt kan ik, neem ik aan, makkelijk collaten via koi8r_general_ci

[ Voor 15% gewijzigd door Verwijderd op 30-10-2008 14:34 ]

[Maasluip]

Dan zou ik wat gaan testen om te kijken of het op een andere manier wel werkt. Zo ja, en je kunt echt niks wijzigen: you're fucked.

[Cartman!]

Misschien helpt het om deze queries uit te voeren voordat je iets anders gaat query-en:

code:

SET NAMES UTF8
SET CHARACTER SET UTF8

[.oisyn]

Je moet kijken of de collation ook goed staat, niet alleen character set! Zo zal utf8_bin bijvoorbeeld niet sorteren op de logische ranschikking van tekens maar gewoon op hun binaire waarde. utf8_general_ci doet dat echter wel op een case insensitive manier, waardoor a == A == Ä.

.edit: oh ik zie al dat dat ook gewoon in je query staat in de topicstart.

[ Voor 12% gewijzigd door .oisyn op 30-10-2008 15:11 ]

[Verwijderd]

Ik heb geprobeerd het te converteren in de query:

code:

"SELECT CategoryID, Category, Notes_PlainText, 
  CAST(SUBSTRING(Notes_PlainText, INSTR(Notes_PlainText, '$lang: ')+LENGTH('$lang: '), 4) AS CHAR CHARACTER SET koi8r) AS vert
  FROM Category WHERE CategoryID IN (".join(",", array_keys($catids)).") ORDER BY vert COLLATE koi8r_general_ci"

evenals

code:

"SELECT CategoryID, Category, Notes_PlainText, 
  CONVERT(SUBSTRING(Notes_PlainText, INSTR(Notes_PlainText, '$lang: ')+LENGTH('$lang: '), 4) USING koi8r) AS vert
  FROM Category WHERE CategoryID IN (".join(",", array_keys($catids)).") ORDER BY vert COLLATE koi8r_general_ci"

Maar ook dit levert niet veel zinnigs op

[ Voor 27% gewijzigd door Verwijderd op 30-10-2008 16:08 ]

[tonyisgaaf]

Om even uit te sluiten dat MySQL fundamenteel het probleem zou zijn heb ik een testcase gemaakt.

• PHP 5.2.5
• MySql 5.0.41

Heb jouw russische teksten ge-copy-paste (at 'random') en daarna gesorteerd op 'text'.

Alle collation (Tabel, DB, Server) op "utf8_general_ci".

Ik vraag me af of het aan de collation ligt, volgens mij heeft dat alleen maar invloed op de encoding van de karakters. Dus ik raad je aan net als ik een uitgeklede testcase te maken (die zo dicht mogelijk in de buurt van de DB komt die je nu aangeleverd krijgt) en dan te gaan experimenteren.

Wat is nu de collation op de row "Notes_Plaintext"? Of anders op de tabel waar die rij in staat?

[.oisyn]

tonyisgaaf schreef op vrijdag 31 oktober 2008 @ 00:39:
Ik vraag me af of het aan de collation ligt, volgens mij heeft dat alleen maar invloed op de encoding van de karakters.

Nee, collation beschrijft juist de volgorde van tekens. De charset is de encoding.
Wikipedia: Collation

[tonyisgaaf]

.oisyn schreef op vrijdag 31 oktober 2008 @ 13:05:
[...]

Nee, collation beschrijft juist de volgorde van tekens. De charset is de encoding.
Wikipedia: Collation

Je hebt gelijk, dat had ik natuurlijk zelf ook kunnen opzoeken
Dan is me nu ook eindelijk duidelijk wat het verschil is tussen charset en collation... had me al vaker over de (schijnbare) overlap verbaasd.

Dan lijkt het er sterk op dat MySQL in het geval van de TS niet de juiste collation toepast op moment van de ORDER BY clausule, alhoewel die wel expliciet wordt opgegeven.

Aan de TS, misschien kun je nog "spelen" met het moment van het expliciet maken van de collation. Zie ook http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html

SQL:

SELECT CategoryID, Category, Notes_PlainText, 
  SUBSTRING(Notes_PlainText, INSTR(Notes_PlainText, '$lang: ')+LENGTH('$lang: '), 4) COLLATE utf8_general_ci AS vert
  FROM Category WHERE CategoryID IN (".join(",", array_keys($catids)).")
  ORDER BY vert COLLATE utf8_general_ci

Alhoewel ik moet toegeven dat ik hier geen onderbouwing voor heb, maar het probleem wel interessant vind .

[Verwijderd]

tonyisgaaf schreef op vrijdag 31 oktober 2008 @ 13:39:
[...]

Je hebt gelijk, dat had ik natuurlijk zelf ook kunnen opzoeken
Dan is me nu ook eindelijk duidelijk wat het verschil is tussen charset en collation... had me al vaker over de (schijnbare) overlap verbaasd.

Dan lijkt het er sterk op dat MySQL in het geval van de TS niet de juiste collation toepast op moment van de ORDER BY clausule, alhoewel die wel expliciet wordt opgegeven.

Aan de TS, misschien kun je nog "spelen" met het moment van het expliciet maken van de collation. Zie ook http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html

SQL:

SELECT CategoryID, Category, Notes_PlainText, 
  SUBSTRING(Notes_PlainText, INSTR(Notes_PlainText, '$lang: ')+LENGTH('$lang: '), 4) COLLATE utf8_general_ci AS vert
  FROM Category WHERE CategoryID IN (".join(",", array_keys($catids)).")
  ORDER BY vert COLLATE utf8_general_ci

Alhoewel ik moet toegeven dat ik hier geen onderbouwing voor heb, maar het probleem wel interessant vind .

Heb putty op UTF-8 gezet om de volgende leesbare output te genereren:

mysql> SELECT CategoryID, Category, SUBSTRING(Notes_PlainText, 
> INSTR(Notes_PlainText, 'russian: ')+LENGTH('russian: '), 4) COLLATE
> utf8_general_ci AS vert FROM Category WHERE CategoryID IN (24,25,28,
> 31,32,34,42) ORDER BY vert COLLATE utf8_general_ci;
+------------+----------------------+------+
| CategoryID | Category             | vert |
+------------+----------------------+------+
|         28 | Algemeen             | Об |
|         31 | Neerlandistiek       | Ни |
|         24 | Ruslandkunde         | Ро |
|         32 | Slavistiek           | Сл |
|         25 | Naslagwerken         | Сп |
|         42 | Periodieken, kranten | Жу |
|         34 | Nederlandkunde       | Ли |
+------------+----------------------+------+