mysql, php - character encoding problem`

I'm doing a project with zend framework and I'm pulling data from a utf-8 databa开发者_Python百科se. 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$$

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:

1. Your database connection uses UTF-8 (execute this query right after your connection is set up: SET NAMES UTF8;)
2. Your PHP code uses UTF-8. That means no using character set translation/encoding functions (no need to when everything is UTF-8).
3. 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.

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.