Setting character encoding on-the-fly for connections CI <-> DataBase (Polish example)

[eluser]Jarek Bolo[/eluser]
Hello

I'm a fresh guy on the forum and also fresh with CI.

I'm representing a nation (Poland) which has non ASCII letters in its alphabet and because of that I encountered problems with encoding.
But this problem isn't related with CI only, this problem always appear whenever you save or pull out data from MySQL database using PHP scripts.

You can set character encoding in META tags to UTF8, you can set MySQL database to use UTF8 but even though you get 'question marks' or other unexpected signs instead of non-ASCII polish letters. You get it either in database tables or on the page.

In Poland we fix it by telling MySQL server just after connecting to it in which encoding communication should it work:

Code:

$dbhandle = mysqli_connect($serveraddress, $username, $pass, $db, $port);
mysqli_query($dbhandle, "SET NAMES utf8");

Thanks to this, MySQL server converts any non-ASCII signs on the fly to UTF8 and saves it in that format, and the same when you access data, MySQL server returns it in UTF8 format.

Above might be a problem to other non-ASCII nations too.

Anyway, I had to modify CI to execute that underlined query just after connecting to database. I did it by putting this query in DB_driver.php file using dbclass function simple_query().
Snippet from DB_driver.php file:

Code:

// Select the database
        if ($this->database != '')
        {
            if ( ! $this->db_select())
            {
                log_message('error', 'Unable to select database: '.$this->database);
            
                if ($this->db_debug)
                {
                    $this->display_error('db_unable_to_select', $this->database);
                }
                return FALSE;
            }
        }
        
        $this->simple_query('SET NAMES utf8');  <-- MINE MODIFICATION (around line number 170-172)
        return TRUE;
    }

What I thought is maybe such kind of setting should be available through database configuration file??

And last thing, just off-topic. Is there any chance to have an option in database configuration file to set port on which you want to connect whit MySQL??

Do wszystkich ziomali czytających to, piszcie do mnie na priva jeśli jakoś inaczej poradziliście sobie z naszymi kochanymi ogonkami w CodeIgniterze.

Greetings.
Jarek Bolo

EDIT:
Well, I've just made some research and... there was quite much about what I wrote :/
But anyway, maybe someone more experienced will tell me if I solved my problem correctly.

[eluser]gtech[/eluser]
RE: mysql port.. somebody has had the same problem.

[url="http://ellislab.com/forums/viewthread/58829"]Thread 58829[/url]

[eluser]Jarek Bolo[/eluser]
[quote author="gtech" date="1194533660"]RE: mysql port.. somebody has had the same problem.

[url="http://ellislab.com/forums/viewthread/58829"]Thread 58829[/url][/quote]

Thanks for reply.
Looking on that link shows that this issue hasn't been resolved yet.
Is there a plan to include this "mysql port setting" option in database config file in next edition of Code Igniter (together with encoding setting)??

[eluser]markos151[/eluser]
You can try settings in your model

example:

class my_model extends Model {

function __construct(){
parent::Model();
$this->db->query("SET character_set_client = latin2");
$this->db->query("SET character_set_connection = latin2");
$this->db->query("SET character_set_database = latin2");
$this->db->query("SET character_set_results = latin2");
$this->db->query("SET character_set_server = latin2");
$this->db->query("SET collation_connection = latin2_general_ci");
$this->db->query("SET collation_database = latin2_general_ci");
$this->db->query("SET collation_server = latin2_general_ci");
}

...
}

[eluser]Jarek Bolo[/eluser]
[quote author="markos151" date="1194547782"]You can try settings in your model

example:
...
}[/quote]

Yes, I also found that way of sorting this problem.
In my opinion better solution is when you don't have to care about it in every model you write.
So following your example we should put these settings in parent Model class.

But best thing will be when CI developers will include it together with port setting option in database config file.

[eluser]gtech[/eluser]

I checked the file at system/database/drivers/mysqli/mysqli_driver.php and noticed that within the ‘db_connect’ function you can add two extra parameters to ‘mysqli_connect’ (dbname and port) and it seems to work ok.

carlton Posted: 17 August 2007 08:19 AM

for now you might have to hack into the file your self.. what I would suggest is log a bug report?

[eluser]kamilko[/eluser]
SET NAMES is not enough: http://ellislab.com/forums/viewreply/259386/

My workaround for mysqli is hack in the mysqli_driver.php

function db_connect should look like this:

Code:

$mysqli = @mysqli_connect($this->hostname, $this->username, $this->password);
$mysqli->set_charset('utf8');
return $mysqli;

'set_charset' must be executed first before anything else after connection is open

Pozdrowienia

[eluser]Jarek Bolo[/eluser]

Code:

$mysqli->set_charset('utf8');

This thing above is actually the same as:

Code:

$mysqli->query('SET NAMES utf8');

Anyway, your workaround is better than mine, it gets deeper to the core and affects only mysqli driver.
In my workaround this query will be executed for every other driver, I have to change it.
Good point.

Pozdrawiam
Jarek Bolo