Thai script sort order

[thellie]

Hi
I don't think I posted about this yet...

I'm building an online aid for people to learn northern Thai dialect - fairly basic, with words, sentences, some information, etc.

This is my senior project for my Thai language degree, and I've come across a web-wide problem with the sort order of Thai words. There is a set criteria in Thai book dictionaries for how to order words, which is fairly complex. But, computers don't seem to be able to replicate this fully.

What I have found on all the websites I've researched so far, which have lists of words in Thai script, is that the ordering is by the first consonant, then vowels.

In Thai most vowels sit on, to the right, or under the first consonant, but there are 5 which sit in front of it. Words beginning with these vowels are put to the end of the sort order. They should however come at the end of the relevant consonant.

What I have done, using Fabrik, is built a sort order table(s), which will sort the words correctly - essentially ensuring that the 5 vowels that come in front of the first consonant, are sorted at the end of that consonant's list.

I've got this far (I hope you have too, and are still reading), but am a bit stumped. The query below is a test to make sure I'm in the right thinking... which I am... I haven't written the final query yet, but that's not a problem.

All I want to know (I think this will complete the job) is how to get the query to run and drop the sort code into a lookup field. But I'm not sure what is the best way - calc plugin? If so, it needs to be framed in php, and I'm hoping you have an example I can look at...?

SELECT SUBSTRING(word,1,1) AS first_character, SUBSTRING(word,2,1) AS second_character, CONCAT(SUBSTRING(word,1,1), SUBSTRING(word,2,1)) AS two_characters

FROM `vocabulary`
WHERE CONCAT(SUBSTRING(word,1,1), SUBSTRING(word,2,1)) IN (SELECT sort_it FROM sort_order_121_repeat)
AND
(SUBSTRING(word,1,1) = '?' OR
SUBSTRING(word,1,1) = '?' OR
SUBSTRING(word,1,1) = '?' OR
SUBSTRING(word,1,1) = '?' OR
SUBSTRING(word,1,1) = '?')

Sorry for yabbering on...

 

[cheesegrits]

Not sure what you mean by "drop the sort code into a lookup field".

Are you trying to sort your list with this?

-- hugh

 

[cheesegrits]

I'm guessing what you are trying to do is set an extra field in your list to be the values generated by that query?

-- hugh

 

[thellie]

Hi Hugh
Yes, my aim is to have the list sorted by this field.

Here are the first 2 consonants of Thai: ? and ?

Here are the 5 vowels which sit in front of them: ? ? ? ? ?

Currently, a simple example sort would look like this:
?
?
??
??
??
??

But it should look like this:
?
??
??
?
??
??

 

[cheesegrits]

In which case, yes you would need a calc element, and you'd need to write the php. Which wouldn't be that much difference, you'd still use substr.

When I'm done with the paid support for today, I'll take look and see if I can give some coding hints.


Sent from my HTC One using Tapatalk

 

[thellie]

Brilliant, thank you

 

[cheesegrits]

I think the functions you would want would be mb_substr() and

http://php.net/manual/en/function.mb-substr.php

... and basically chop up and re-assemble the string in much the same way you are doing the comparison in SQL.

You can use a placeholder for the 'word' element.

I'm still kind of busy, I'll try and find some time later.

-- hugh

 

[thellie]

Whenever is convenient, I'm just happy for the help

I checked my php.ini and enabled the following lines, based on the mb_substr() usage... is that correct?:

extension=php_mbstring.dll
mbstring.func_overload = 7

No need to answer straight away - do the important stuff that pays the bills...

 

[cheesegrits]

Enabling mbstring (php's multi byte string library) is kind of version and install dependent. Probably best to talk to your host support if that didn't work. Most modern installs have it as standard.

You might be able to get away without it, but I'm assuming Thai is a multi byte character set, so using the mbstring functions just makes sense.

Hugh


Sent from my Nexus 7 using Tapatalk

 

[thellie]

Ah okay... I'll wait for your coding hints, and then see what happens. I think you're correct about Thai being multibyte.

 

[troester]

See http://fabrikar.com/forums/index.php?wiki/calculation-element/ for the calculation element.
Set "only calc on save" = yes and (for testing) "show in list" = yes.
This way in the list it displays the values stored in the database and used for ordering.

The calculated value is stored in the database only if you are saving a record. So if there are already existing records you must open and re-save or fill the value in the database manually (e.g. with phpMyAdmin).