I got weird characters extracting data from MySQL db

Question

Hi there!

Well, I got a MySQL db, encoded as utf8_unicode_ci, and it runs like a charm with the current application (written in Code Igniter)

Now, I'm developing a new PHP app, and when I try to recover the data, several characters are unreadable - chars appears ok in the DB with phpMyAdmin, but when I try to put it up in a webpage, it became like "ROLA �60".

These characters are spanish letters, such as ñ, á, Ó... or ascii codes like €, Ø...

Where's the problem? I've set the page as meta http-equiv="Content-Type" content="text/html; charset=UTF-8", I've tried the mysql_set_charset() function, and still nothing.

Any experience with this kind of problems?

Answer 1

I have noticed this exact problem in my database driven applications, and it took me a long time to work it out!

The problem occurs because there are at least three places in your application that need their character sets defining, and they must all be the same character set (and that character set must be able to handle the characters you are handling).

The question mark symbol and it's variations occurs when the browser doesn't understand what character it is being passed.

Make sure your character sets match in the following places:

1. The HTML head section.
2. The database collation itself - this can be set in phpMyAdmin when you create a new table, or alter the schema of an existing table.
3. The most overlooked character set setting: The php.ini file's "default_charset" value (can be set via PHP script or the httpd.conf file).

Judging from your post, it looks like your PHP configuration might be set to be using a different default_charset. This means that your database will be storing the characters fine, and will be sendign the characters fine to your script, but the PHP script itself will not know what to do with the character, and thus outputs to the browser as the annoying question mark symbol.

Try changing the php.ini value to the same charset, and you may be surprised to see the characters displaying fine! If you don't have access to php.ini, you can change the value with the following function:

ini_set("default_charset", "UTF-8");

Hope this helps.

Answer 2

if mysql_set_charset() didn't work try executing

mysql_query('SET NAMES utf8');

after establishing connection.

Answer 3

Any kind of problem requires only one experience: understanding of what are you doing and debugging. Very rare skills nowadays, thanks to sites like stackoverflow.

there are 3 levels where encoding gets involved:

• database level
• server side script level.
• html page level.

each should be checked respectively.

to check HTML level is easy. Just click "View" menu in your browser, then "encoding" and see which one is marked. If it's right one, you are all right. If not - wrong HTTP header being sent and you have to make it right

for the server side there is very little to do. Just to tell mysql which encoding you want. it can be done 2 ways:

• mysql_set_charset() - preferred one
• SET NAMES query

note that UTF-8 encoding named utf8 in mysql.

database level seems O.K. in your case.