Danish characters in frontend..

[kemster]

Hey,

I have tried to finde an answer for a couple of days now, but with no luck..

I want to sort my news articles with danish characters, but I can't make it happen. A normal sort is not possible - I use UTF-8, and it is a multiple languages site..

It sorts like this: aåæøbcde.. Because å=Ã¥, æ=æ and ø=ø - so it sees it as an A..

How can I get åæø to end at the end op the alfabet/list?

Someone can help me?

Thanks!

[thorn]

Hello,

due to an "error in design", there is no way to achieve this, especially in a multi-language environment.
[You would have to install wb again, and to patch the core. -- Then, it may work as expected (depends on installed modules) -- totally untested]

As workaround you may use a temporary DB-table to sort the results (not allowed by some Providers),
or use sort with SORT_LOCALE_STRING and setlocale() (some providers forbid the use of sort on large arrays).

thorn.

[thorn]

Hello,

after some tests, i found this workaround:

Code: [Select]

$database->query("SET NAMES utf8 ");
if($query = $database->query("SELECT CAST(CAST(`title` AS BINARY) AS CHAR) as t FROM `".TABLE_PREFIX."mod_news_posts` ORDER BY t ")) {
  while($res = $query->fetchRow()) {
    var_dump($res);
  }
}
$database->query("SET NAMES DEFAULT ");

thorn.

[kemster]

Well it seems like setting the titles in the right order. So thanks for now. But I have to puzzle to set in the array in the correct places, so it makes the correct layout instead of just a bunch of strings..

If you have any suggestions on the way it would be nice..

I never like to smack in a lot of code in a forum, so the query I want to sort in "view-file" is the "Post loop" in line 124.

I'm not a newbie, but also not the best programmer in the world..

[thorn]

Hello,

just add the "CAST(...) as t" string after the *

Code: [Select]

$query_posts = $database->query("SELECT * FROM ".TABLE_PREFIX."mod_news_posts   [...]becomes

Code: [Select]

$query_posts = $database->query("SELECT *, CAST(CAST(`title` AS BINARY) AS CHAR) as t FROM ".TABLE_PREFIX."mod_news_posts   [...]

And replace all occurrences from $post['title'] with $post['t'] around line 215, (four times)

This workaround assumes that the used WYSIWYG-Editor will store _all_ Umlauts as Entities. Otherwise it will break the content.

thorn.

EDIT:
BTW:

Code: [Select]

ORDER BY t COLLATE utf8_unicode_ci may match your needs more than the default utf8_general_ci-ordering.

[kemster]

Hey Thorn,

It didn't work. You're suggestion is a good one, and I thank you very much for helping me - qucik respons and a super explanation.

The query works and the content is shown as normal, but it is not sortet correctly.. I have an idea of trying another encoding than binary, because it probably looks at the A character (å=Ã¥), don´t you think? I just didn't had the time to try so much yesterday..

[thorn]

Hello,

if all went well, and the only problem is the sorting-order, try

Code: [Select]

ORDER BY t COLLATE utf8_unicode_cior, if this doesn't match, try

Code: [Select]

ORDER BY t COLLATE utf8_danish_ci
In a multi-language environment, you may use specialised COLLATE-Strings for every different language.
See http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html

thorn.

[kemster]

Hello thorn,

I have been puzzling the last few days, and tried to find a way. You're suggestion to order by COLLATE is not possible, because the result is zero strings..

I have found a way:

Code: [Select]

CAST(CAST(`title` AS BINARY) AS CHAR CHARACTER SET utf8) as t and then use the collate. But the there is still a sorting problem. I have tried several combinations, but there are two different favorits:

1

Code: [Select]

CAST(CAST(`title` AS BINARY) AS CHAR CHARACTER SET utf8) as tand

Code: [Select]

ORDER BY t COLLATE utf8_danish_ciand set collation for table ('title') to latin1_danish_ci.
Result: It sorts right, but the danish characters are shown as �.

2
Same coding, but collations for table ('title') is set to utf8_danish(or unicode)_ci.
Result: The danish characters are shown correct, but it sorts wrong..

Aarrgghhh..!!

Do you have any more suggestions? Maybe on how to get the danish characters right in option 1?

Kenneth

[thorn]

Hello,

did you use SET NAMES utf8?

I think if you try exactly this, than it will do correctly

Code: [Select]

$database->query("SET NAMES utf8 ");
if($query = $database->query("SELECT CAST(CAST(`title` AS BINARY) AS CHAR) as t FROM `".TABLE_PREFIX."mod_news_posts` ORDER BY t COLLATE utf8_unicode_ci")) {
 while($res = $query->fetchRow()) {
  [... your code here ...]
  }
}
$database->query("SET NAMES DEFAULT ");
Do not alter Table-Charset! (keep latin1)
You may choose utf8_danish_ci instead of utf8_unicode_ci in the ORDER BY-clause.

thorn.

[kemster]

Hello again,

I'm sorry Thorn - but are totally right!! Hehe.. Very cool. My mistake was using the SET NAMES and NAMES DEFAULT query on the wrong places.. Just for info for others (danish) users:

View file for news module

Line 114

Code: [Select]

$database->query("SET NAMES utf8 ");

    // Query posts (for this page)
    $t = time();
    $query_posts = $database->query("SELECT *, CAST(CAST(`title` AS BINARY) AS CHAR) as t FROM ".TABLE_PREFIX."mod_news_posts
        WHERE section_id = '$section_id' AND active = '1' AND title != ''$query_extra
        AND (published_when = '0' OR published_when <= $t) AND (published_until = 0 OR published_until >= $t)
        ORDER BY t COLLATE utf8_danish_ci ASC".$limit_sql);
    $num_posts = $query_posts->numRows();
Line 213

Code: [Select]

               if(isset($users[$uid]['username']) AND $users[$uid]['username'] != '') {
                    if($post_long_len < 9) {
                        $values = array(PAGE_TITLE, $group_id, $group_title, $group_image, $display_group, $display_image, $post['t'], $short, $post_link, $post_date, $post_time, $publ_date, $uid, $users[$uid]['username'], $users[$uid]['display_name'], $users[$uid]['email'], '');
                    } else {
                        $values = array(PAGE_TITLE, $group_id, $group_title, $group_image, $display_group, $display_image, $post['t'], $short, $post_link, $post_date, $post_time, $publ_date, $uid, $users[$uid]['username'], $users[$uid]['display_name'], $users[$uid]['email'], $TEXT['READ_MORE']);
                    }
                } else {
                    if($post_long_len < 9) {
                        $values = array(PAGE_TITLE, $group_id, $group_title, $group_image, $display_group, $display_image, $post['t'], $short, $post_link, $post_date, $post_time, $publ_date, '', '', '', '', '');
                    } else {
                        $values = array(PAGE_TITLE, $group_id, $group_title, $group_image, $display_group, $display_image, $post['t'], $short, $post_link, $post_date, $post_time, $publ_date, '', '', '', '', $TEXT['READ_MORE']);
                    }
                }
                echo str_replace($vars, $values, $setting_post_loop);
            }
        }
    }

$database->query("SET NAMES DEFAULT ");
Many, many thanks to you Thorn! You're the man!

\Kenneth