mysql, php - character encoding problem`

Question

I'm doing a project with zend framework and I'm pulling data from a utf-8 database. The project is utf-8 as well.

In a form, I have a select element displaying a list of countries. The problem is: In french or spanish, some countries are not displayed.

After doing a var_dump() of my country list, I saw that those were the countries with special characters. Accented ones.

in the var_dump I could see the character represented as a ? in a diamond. I tried changing the encoding to iso-8859-1 and I could see the var_dump result with the special characters just fine.

How come data coming from a utf-8 database are displaying in iso-8859-1!

Can I store iso-8859-1 character set in a utf-8 table in mysql without problem? Shouldn't it display messed up characters?

confused.

--

delimiter $$

CREATE TABLE `geo_Country` (
  `CountryID` int(10) NOT NULL,
  `CountryName` varchar(45) NOT NULL,
  `CountryCompleteName` varchar(45) NOT NULL,
  `Nationality` varchar(45) NOT NULL,
  `CreationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Status` tinyint(1) NOT NULL DEFAULT '1',
  `LanguageCode` char(2) NOT NULL,
  `ZoneID` int(10) NOT NULL,
  PRIMARY KEY (`CountryID`,`LanguageCode`),
  KEY `fk_geo_Country_web_Language1` (`LanguageCode`),
  KEY `fk_geo_Country_geo_Zone` (`ZoneID`),
  KEY `idx_CountryName` (`CountryName`)
  CONSTRAINT `fk_geo_Country_geo_Zone` FOREIGN KEY (`ZoneID`) REFERENCES `geo_Zone` (`ZoneID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_geo_Country_web_Language1` FOREIGN KEY (`LanguageCode`) REFERENCES `web_Language` (`LanguageCode`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

Could you please post SHOW CREATE TABLE mytable for the table involved and the relevant part of the PHP code you are running? — Quassnoi
– Quassnoi, Commented Apr 15, 2011 at 11:10
I tried changing the encoding to iso-8859-1 - what encoding you tried to change and how? — Your Common Sense
– Your Common Sense, Commented Apr 15, 2011 at 11:18

Peter Kruithof · Accepted Answer · 2011-04-15 11:33:45Z

3

The thing to remember with UTF-8 is this:

Everything in your entire application needs to be UTF-8!

For a normal PHP/MySQL web application (a form, posting to a database), you need to check if:

Your database connection uses UTF-8 (execute this query right after your connection is set up: SET NAMES UTF8;)
Your PHP code uses UTF-8. That means no using character set translation/encoding functions (no need to when everything is UTF-8).
Your HTML output is UTF-8, by either sending a Content-Type: text/html; charset=utf8 header, of using a <meta charset="utf8"> tag (for HTML5, for other HTML variants, use <meta http-equiv="Content-Type" content="text/html; charset=utf8">)

In your case of var_dump'ing, there is just some plain text that is sent to the browser, without any mention of a character set. Looking at rule #3, this means your browser is displaying this in a different character set, presumably latin1, thus giving you the diamonds/question marks/blocks.

If you need to check if your data is stored properly, use a database client like PHPMyAdmin to view the record. This way you're viewing the content as UTF-8 (NOTE: this is a setting in PMA, so check if it is not set to a different charset!).

On a side note, set the collation of your databases' text columns to utf8_general_ci, this is not used for storing, but for sorting. So this isn't related to your problem, but it's a good practice to do so.

edited Apr 15, 2011 at 11:33

answered Apr 15, 2011 at 11:27

Peter Kruithof

10.9k6 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Nicolas de Fontenay Over a year ago

I think that 1) is my problem. I'm using zend framework and following Col. Shrapnel information I should configure that. Only, I use a configuration file and not a variable for my parameters. 2) that's good. 3) that's good. My data did come from a iso-8859-1 database. I've loaded them with kettle and modified their encoding on the fly to utf-8. The test look-at-data-directly-in-db is successful (MSQL workbench!). So they are indeed utf-8 now. This answer deserves at least a thumbs up even if my problem is not completely fixed yet!

Nicolas de Fontenay Over a year ago

ok. I thought about it and it's the answer. I just have another question for zend framework people. Thanks a lot :)

Your Common Sense Over a year ago

There is not a single reason to have Everything in UTF-8. You can have the pafe in latin1 while your database in utf. it's not a big deal and allowed by design

Your Common Sense Over a year ago

and these <meta> tags has nothing to do with encoding. Only real HTTP header do

Peter Kruithof Over a year ago

When displaying utf-8 data in a page that has a latin1 charset, special characters are displayed wrong, because they're not in the character set. The only way to do this, is by re-encoding the text to latin1 before outputting it. But I don't see any reason to do so.

|

Your Common Sense · Accepted Answer · 2011-04-15 11:36:30Z

1

When connecting to database you should set up cleint encoding.
for Zend_Db it seems should be like this (notice 'driver_options'):

$params = array(
    'host' => 'localhost',
    'username' => 'username',
    'password' => 'password',
    'dbname' => 'dbname',
    'driver_options' => array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8;');
);

for the application.ini

resources.db.params.charset = utf8

as a last resort you could just run this query SET NAMES UTF8 manually just like any other query.

edited Apr 15, 2011 at 11:36

answered Apr 15, 2011 at 11:17

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

3 Comments

Nicolas de Fontenay Over a year ago

yeah I was looking around and it seems there waqs a mysql_query(SET NAMES UTF8) to add. I will try that. I use a config file for my connection. I will see how it goes.

Nicolas de Fontenay Over a year ago

I tried my luck with this in the application.ini file: resources.db.params.driver_options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8;') with no success. I had a gut feeling an array like that in a string wouldn't work well lol.

Your Common Sense Over a year ago

you know, i never used ZF myself. just googled for zend framework database encoding

Collectives™ on Stack Overflow

mysql, php - character encoding problem`

2 Answers 2

9 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related