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!
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.
Hello,
after some tests, i found this workaround:
$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.
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..
Hello,
just add the "CAST(...) as t" string after the *
$query_posts = $database->query("SELECT * FROM ".TABLE_PREFIX."mod_news_posts [...]
becomes
$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: ORDER BY t COLLATE utf8_unicode_ci may match your needs more than the default utf8_general_ci-ordering.
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..
Hello,
if all went well, and the only problem is the sorting-order, try
ORDER BY t COLLATE utf8_unicode_ci
or, if this doesn't match, try
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.
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:
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
CAST(CAST(`title` AS BINARY) AS CHAR CHARACTER SET utf8) as t
and
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
Hello,
did you use SET NAMES utf8?
I think if you try exactly this, than it will do correctly
$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.
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
$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
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