Problems with display of UTF-8 encoded content from a DB

Question

Dear members of the Stackoverflow community,

We are developing a web application using the Zend Framework, and we are facing some encoding issues that we hope you might help us solve. The situation goes something like this: There are certain tables on a MySQL database that need to be displayed as html. Because the site is designed using the Spanish language, the database contains some characters like "á" or "ñ". Our internal policy is to set all the encodings as UTF-8, including all the databases and the tables. The problem is, that when we retrieve the content from the DB, some characters are displayed as question marks.

We are out of ideas. These are all the things that we have already tried and double-checked: 1. The SQL file from which we load all the data is properly UTF-8 encoded. 2. The SQL is loaded through phpmyadmin (which is configured as UTF-8), and the resulting tables are displayed properly. 3. The netbeans environment used for coding is also set as UTF-8.

The weird thing is that all the content that is hard-coded either as php or html is displayed properly. Only the values that are extracted from the database have issues.

Any ideas?

Thank you very much.

Answer 1

It’s hard to tell without any code. Maybe one utf8_encode() too much? There are ways to force UTF-8 compatibilty – did you tried that?

Answer 2

do you use

resources.db.params.charset = "utf8" 

in your config file like (application.ini)?

Answer 3

How did you try to extract? CLI mysqldump OR phpmyadmin extract?

I had similar problems, even one time i extracted manualy (by coping DB with SELECT & INSERT).

Usualy this command solved problem mysqldump --set-character-set=latin-1 --set-charset -u user -pPASSWORD databasename > databasename-latin1.sql

Try also: http://www.saiweb.co.uk/mysql/converting-mysql-latin1-to-utf8

Answer 4

I don't know if this is the right approach, but to fix this I just use a different document encoding when printing the latin characters from the database:

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

I am certainly not sure why the UTF-8 encoding doesn't work. I am looking forward for the right answer too.

Answer 5

Make sure your connection is set to utf-8 and just after you make a connection to the database issue the following sql

"SET NAMES 'utf8';"

Answer 6

Thanks guys for the quick replies (I'm a member of webmaster_lookup's team). Here is what I have to say about your responses:

1.- Toscho: I think I don't really understand what you mean by your reply. We are getting the data using the default Db_Table models from Zend, connected to our database.

2.- User1400: We didn't have that line. I just added it and it made no difference.

3.- Confiq: We accesed the data through php. More specifically, using the default functionality provided by the db_table class of the Zend Framework.

4.- Danny Herran: I tryed changing the html charset to "iso-8859-1" and the DB content was displayed properly, but the rest of the page was not.

In summary, somehow the db output is being read as "iso-8859-1", even considering that the tables themselves are set to utf8.

More ideas?

Thanks!

Answer 7

First of all you need to identify where encoding is get broken. I did not find clear answer on this in your post. You need to ensure:

1. Table encoding is properly set
2. DB Connection is configured properly (i.e. SET NAMES 'utf8' issued)
3. Zend views configured: resources.view.encoding = "UTF-8"
4. Apache configured right

Supposing all the above is checked, probably this would help. Add the following line to .htaccess:

addDefaultCharset utf-8

Answer 8

I did had the same problem once ago but i was using these options

• Php 5.3
• PDO

turns out that pdo had bugs in PDO::MYSQL_ATTR_INIT_COMMAND for example try :

try {
    $pdbo = new PDO("mysql:host=localhost;dbname=test", 'root', '', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
}catch(PDOException $e){
    echo $e->getMessage();
}

source : http://bugs.php.net/bug.php?id=47224

hint : use Mysqli its worked for me