CodeIgniter Forums
[solved] Very strange Mysql/UTF8/accents problem. I need your help - 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: [solved] Very strange Mysql/UTF8/accents problem. I need your help (/showthread.php?tid=9256)



[solved] Very strange Mysql/UTF8/accents problem. I need your help - El Forum - 06-18-2008

[eluser]Kopel[/eluser]
Hi,

I'm developing a multilang website on Codeigniter 1.6.1.
A part of the website is in French, so i have to deal with accents and special characters...

Everything is running fine on my local server.
But online, accents and special characters coming from database results are replaced with strange things like è, é, ê, etc...

So i've created a clean script with a select query without using the codeigniter db driver:
Code:
<?php
$db = mysql_connect('xxx', 'xxx', 'xxx');
mysql_select_db('xxx',$db);
$sql = 'SELECT * FROM news';
$req = mysql_query($sql) or die('');

while($data = mysql_fetch_assoc($req)) {
  print_r($data);
}
mysql_close();
?>

This script returns the text with accents and special caracters not altered.
So, the problem is coming from the codeigniter db driver.


Here is the (ugly) solution that i found.
On the system\database\drivers\mysql\mysql_driver.php file, i've changed:

Code:
function db_set_charset($charset, $collation)
{
  return @mysql_query("SET NAMES '".$this->escape_str($charset)."' COLLATE '".$this->escape_str($collation)."'", $this->conn_id);
}

To:
Code:
function db_set_charset($charset, $collation)
{
  return True;
}

As you can see this is a pretty bad solution.
At least i can move on with my project...
But i definitely do not want to let this ugly fix into the final code!

I don't understand where the problem is coming from.
Do you have any clue?
Please help me to find the real origin of the problem.



Here is how codeigniter is setup:

My html header have:
Code:
<meta http-equiv="content-type" content="text/HTML; charset=utf-8" />

Into my config.php:
Code:
$config['charset'] = "UTF-8";


Into my database.php:
Code:
$db['production']['char_set'] = "utf8";
$db['production']['dbcollat'] = "utf8_general_ci";


And all my Mysql tables are set to: collation utf8_general_ci


Let me know if you have any idea or if need more information.

Thanks!

Kopel


[solved] Very strange Mysql/UTF8/accents problem. I need your help - El Forum - 06-18-2008

[eluser]Crimp[/eluser]
You can check if the encoding of your actual file is UTF-8.


[solved] Very strange Mysql/UTF8/accents problem. I need your help - El Forum - 06-18-2008

[eluser]Kopel[/eluser]
Hi Crimp,

Thanks for your message.

Which file should i check the encoding?
I mean, the html pages are generated on the fly by Codeigniter.
So i don't know what to do.

There is no problem if i type some accents or special characters directly into a view.
The problem appears only when i display some texts from the database.

When i send emails there is no problem at all, accents are correctly displayed in the messages.
But! Not with my ugly fix!
When i send emails with my fix, accents are replaced by this: �

So i think i'm in big trouble right now, because i can't get it running correctly on the website pages + mails at the same time...


[solved] Very strange Mysql/UTF8/accents problem. I need your help - El Forum - 06-18-2008

[eluser]Crimp[/eluser]
Check all the files in the chain -- I to O.

Best forget about hacking the CI core to fix this; CI works. That's 100% guaranteed.

The problem is of course that the encoding is converted, in that elusive somewhere.

From what you list, UTF-8 is set in the right places.

Which leaves a chance that a file is accidentally saved with another encoding.

Files should be UTF-8, no byte order mark.

You could also, as another example, do a dump of the db to make triple sure that the settings are set. Some GUIs can be a little unreliable on the display side here.

Yet another thing, not mentioned, to always check is the http headers. Again, UTF-8?

Of course, the latter does not seem to apply as the problem appears to be specific to certain methods.

Usually something like this is not big trouble. It's just a simple oversight. The kind where you slap your forehead repeatedly when it's sorted.


[solved] Very strange Mysql/UTF8/accents problem. I need your help - El Forum - 06-19-2008

[eluser]Kopel[/eluser]
Thanks Crimp for all these clues.

Well, I'm more and more convinced that the problem is coming from the configuration of my host server (apache or php, i don't know).

I've tested my site (same files) on two different servers (same hosting company).
Theses 2 installations are connected to the same database.

After some tests, i can concluded the accents problem only appears on one server.
When i check the header response of the same page, i get UTF-8 on one and ISO-8859-1 on the other.
It seems the server is forcing the encoding to ISO.

I've contacted my host company to report the issue...

----

[strike]But, i still have a doubt on something...
How the accents should be encoded inside the database?[/strike]

[strike]I have done a dump of my database into a .sql file through PhpMyAdmin.
When i open the file i can see the accents are stored this way "é" instead of "é".
Is it stored the correct way?[/strike]

EDIT: Special characters are correctly displayed when i open the .sql into a UTF-8 text editor...


[solved] Very strange Mysql/UTF8/accents problem. I need your help - El Forum - 06-19-2008

[eluser]Kopel[/eluser]
Okay, i found the solution!

I have to force the utf-8 charset into the header:
Code:
header('Content-Type: text/html; Charset=UTF-8');

So, i definitely think my Apache is configured to force ISO-8859-1 encoding.
I'm on a shared server, don't know if i can check this.

(happy happy happy)


[solved] Very strange Mysql/UTF8/accents problem. I need your help - El Forum - 11-27-2008

[eluser]Unknown[/eluser]
*edited*

I should make sure that I am confident my original source material is the right encoding before I go on forums and make an ass of myselfTongue

/*edited*


[solved] Very strange Mysql/UTF8/accents problem. I need your help - El Forum - 11-30-2008

[eluser]Paul Mastin[/eluser]
Useful post. My problem lie with my development server's compilation of apache. Wasn't configured to send utf as the encoding type. Here is a great resource for understanding character encoding: UTF-8 Encoding Resource

On another note, Code Igniter is just a piece of software. No piece of software is ever 100% guaranteed to work Wink I recently discovered something driving me crazy that was a CI bug (File uploads and the encryption setting). Not to say it isn't a well written, supported piece of scripting, but cannot forget that any piece of software has bugs.

Gezuar,

Paul