Author Topic: Danish characters in frontend..  (Read 5883 times)

Offline KCM

  • Posts: 10
Danish characters in frontend..
« on: January 06, 2009, 07:21:17 PM »
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!
« Last Edit: January 11, 2009, 09:53:51 PM by KCM »

Offline thorn

  • Posts: 980
  • Gender: Male
    • Projects
Re: Characters in frontend..
« Reply #1 on: January 06, 2009, 07:51:27 PM »
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.

Offline thorn

  • Posts: 980
  • Gender: Male
    • Projects
Re: Characters in frontend..
« Reply #2 on: January 06, 2009, 09:12:12 PM »
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.
« Last Edit: January 06, 2009, 09:15:39 PM by thorn »

Offline KCM

  • Posts: 10
Re: Characters in frontend..
« Reply #3 on: January 06, 2009, 09:53:29 PM »
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..

Offline thorn

  • Posts: 980
  • Gender: Male
    • Projects
Re: Characters in frontend..
« Reply #4 on: January 06, 2009, 10:35:15 PM »
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.
« Last Edit: January 07, 2009, 06:15:46 PM by thorn »

Offline KCM

  • Posts: 10
Re: Characters in frontend..
« Reply #5 on: January 08, 2009, 08:13:40 AM »
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..

Offline thorn

  • Posts: 980
  • Gender: Male
    • Projects
Re: Characters in frontend..
« Reply #6 on: January 08, 2009, 07:09:09 PM »
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.

Offline KCM

  • Posts: 10
Re: Characters in frontend..
« Reply #7 on: January 11, 2009, 07:52:05 PM »
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
« Last Edit: January 11, 2009, 07:54:21 PM by KCM »

Offline thorn

  • Posts: 980
  • Gender: Male
    • Projects
Re: Characters in frontend..
« Reply #8 on: January 11, 2009, 08:07:55 PM »
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.

Offline KCM

  • Posts: 10
Re: Characters in frontend..
« Reply #9 on: January 11, 2009, 09:53:21 PM »
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! :mrgreen:

\Kenneth

 

postern-length