Danish characters in frontend..

[KCM]

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 Expand


$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 Expand

               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

[thorn]

Hello,

did you use SET NAMES utf8?

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

Code Select Expand


$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.

[KCM]

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 Expand

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 Expand

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

Code Select Expand

ORDER BY t COLLATE utf8_danish_ci
and 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,

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

Code Select Expand

ORDER BY t COLLATE utf8_unicode_ci
or, if this doesn't match, try

Code Select Expand

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.

[KCM]

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,

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

Code Select Expand


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

Code Select Expand


$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 Expand

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

[KCM]

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,

after some tests, i found this workaround:

Code Select Expand


$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.

[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.

[KCM]

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!