Problem sorting UTF-8 datasets in MYSQL

Problem sorting UTF-8 datasets in MYSQL - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Problem sorting UTF-8 datasets in MYSQL (/showthread.php?tid=51882)

Problem sorting UTF-8 datasets in MYSQL - El Forum - 05-21-2012

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

Problem sorting UTF-8 datasets in MYSQL - El Forum - 05-21-2012

[eluser]weboap[/eluser]
http://philsturgeon.co.uk/blog/2009/08/utf-8-support-for-codeigniter

hope it help

Problem sorting UTF-8 datasets in MYSQL - El Forum - 05-21-2012

[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

Problem sorting UTF-8 datasets in MYSQL - El Forum - 05-23-2012

[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