Problem sorting UTF-8 datasets in MYSQL

[eluser]monoclonal[/eluser]
Hi everyone,

I have a MySQL database which I have set up as a UTF-8 charset and utf8_unicode_ci collation as the data I need to store contains greek letters (alpha, beta etc) as well as other funky symbols and whatnot. The problem I am having is that when selecting data [SELECT * FROM mytable ORDER BY myutf8column] and echoing in php view the table is not sorted properly. For example, this is what i get when ORDER BY ASC on this column;

Potassium
Suramin
β-Estradiol
(±)-Epinephrine
6-Carboxyfluorescein
Acetylcholine chloride
Boric acid

I am using codeigniter 2.1, the database connection settings are using UTF-8 charset and utf8_unicode_ci collation, which is what the database is also using.

If I do the query in phpmyadmin it works fine.
I've tried the query from php/CI using

Code:

$this->db->query("SELECT * FROM table ORDER BY column")

as well as

$this->db->order_by("column")
$this->db->select("SELECT * FROM table")

Just a thought, does the order of order_by() and select() matter?
Both methods give the incorrect sorting. Any suggestions?

[eluser]weboap[/eluser]
http://philsturgeon.co.uk/blog/2009/08/u...odeigniter


hope it help

[eluser]monoclonal[/eluser]
Hi,
I believe I have all the config settings pointing to UTF-8 for both CI and for MYSQL ie,

Code:

//config.php
$config['charset'] = "UTF-8";

//and in database.php
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_unicode_ci';

headers are sent using html5, utf-8, but they display fine, so it isn't that.

EDIT:
someone has tried using kohana on another forum and it works fine for them. This is their post:
"Works fine here using Kohana ...

Code:

function action_test()
{
$query = DB::select()->from('terse')->order_by('text', 'ASC');
echo $query.'<br />';
$result = $query->execute('alternate');
foreach($result as $row)
{
echo $row['text'].'<br />';
}
}

Result::

SELECT * FROM `terse` ORDER BY `text` ASC
(±)-Epinephrine
6-Carboxyfluorescein
Acetylcholine chloride
Boric acid
Potassium
Suramin
β-Estradiol

I would assume its a CodeIgniter problem"

although, technically, beta-estradiol should be under 'b' for beta, but that's ok

[eluser]monoclonal[/eluser]
Problem solved!

The problem was because I hadn't trimmed (trim()) the white space in front of the values that were out of order. My apologies! All solved. I still [heart] CI