DB charset/collation problem - 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: DB charset/collation problem (/showthread.php?tid=18470) |
DB charset/collation problem - El Forum - 05-07-2009 [eluser]serans1[/eluser] Hi, i have been using CI for few weeks now and i have to say it looks good, i tried to look for an answer in the forums but did not find a solution to my problem (one that not requires hacking of the CI core). I have a DB with non en languages all fields,tables,and DB collation are set to utf 8. CI Version 1.71. CI config have : $config['charset'] = "UTF-8"; DB config have : $db['default']['char_set'] = "utf8"; $db['default']['dbcollat'] = "utf8_general_ci"; and i am getting grarbage instead of text on non english language. the same script returns good data when connecting directly and not running through CI. Hope you can help, Thanks DB charset/collation problem - El Forum - 05-07-2009 [eluser]serans1[/eluser] I got into it and I see that the chars are being corrupted once i am connecting to the database and getting the instance (I work with more then one DB). the line $db=$this->load->database($config, TRUE); makes the charset goes bad and config is: $config['char_set'] = "utf8"; $config['dbcollat'] = "utf8_general_ci"; $config['hostname'] = "x"; $config['username'] = "x"; $config['password'] = "x"; $config['database'] = "x"; $config['dbdriver'] = "mysql"; $config['dbprefix'] = ""; $config['pconnect'] = FALSE; $config['db_debug'] = FALSE; DB charset/collation problem - El Forum - 05-07-2009 [eluser]serans1[/eluser] Nailed down. CI perform the following query : SET NAMES 'utf8' COLLATE 'utf8_general_ci' System collation & charset before the query are : Array ( [Variable_name] => character_set_client [Value] => latin1 ) Array ( [Variable_name] => character_set_connection [Value] => latin1 ) Array ( [Variable_name] => character_set_database [Value] => utf8 ) Array ( [Variable_name] => character_set_filesystem [Value] => binary ) Array ( [Variable_name] => character_set_results [Value] => latin1 ) Array ( [Variable_name] => character_set_server [Value] => latin1 ) Array ( [Variable_name] => character_set_system [Value] => utf8 ) Array ( [Variable_name] => character_sets_dir [Value] => /usr/share/mysql/charsets/ ) Array ( [Variable_name] => collation_connection [Value] => latin1_swedish_ci ) Array ( [Variable_name] => collation_database [Value] => utf8_unicode_ci ) Array ( [Variable_name] => collation_server [Value] => latin1_swedish_ci ) and after : Array ( [Variable_name] => character_set_client [Value] => utf8 ) Array ( [Variable_name] => character_set_connection [Value] => utf8 ) Array ( [Variable_name] => character_set_database [Value] => utf8 ) Array ( [Variable_name] => character_set_filesystem [Value] => binary ) Array ( [Variable_name] => character_set_results [Value] => utf8 ) Array ( [Variable_name] => character_set_server [Value] => latin1 ) Array ( [Variable_name] => character_set_system [Value] => utf8 ) Array ( [Variable_name] => character_sets_dir [Value] => /usr/share/mysql/charsets/ ) Array ( [Variable_name] => collation_connection [Value] => utf8_general_ci ) Array ( [Variable_name] => collation_database [Value] => utf8_unicode_ci ) Array ( [Variable_name] => collation_server [Value] => latin1_swedish_ci ) summary CI sets the encoding and names by default and if you migrating to CI and have two pieces of codes one works with CI and the other w/o then you need to make sure that names and charsets are matching - it will not use the default as stated in the db settings. to view your db(mysql) settings you can query: SHOW VARIABLES LIKE 'character_set%'; SHOW VARIABLES LIKE 'collation%'; |