Willing wheels

I had hell a hard time debugging this. I served an utf8 query string to insert some data, and when data were inserted (through Zend_Db_Adapter calls), it got inserted into db as non-utf8 strings!

Worst part of this is that my db (and my table as well as the particular row in my table where I had this issue) were configured with utf8 as default charset AND uft8_general_ci as collation.

Anyway, the solution I found (quick fix for now), is to explicitly tell the mysql connection resource that all queried string is on utf8 by prepending ‘SET CHARACTER SET utf8;‘ to my sql query.

It would be better to find a way to automatically run this when sql resource is created so that all queries are properly treated as utf8, but so far it does the trick for me.
If I find a better place where to put it, I will update this post.

[UPDATE]
I’ve found a better way where to specify which charset to use. It looks like since v 1.8.0, ZF natively handles this, simply provide a ‘charset’ option with appropriate value as done below:

db.adapter = PDO_MYSQL
db.params.host = localhost
db.params.dbname = mydbname
db.params.username = myusername
db.params.password = mypwd
db.params.charset = utf8

(see http://framework.zend.com/issues/browse/ZF-1541)

sources

• http://dev.mysql.com/doc/refman/5.1/en/set-option.html mysql official doc (see section 12.5.4 SET syntax)
• http://blogs.vinuthomas.com/2006/12/22/modifying-your-mysql-databases-to-be-utf-8-compliant/
• http://framework.zend.com/issues/browse/ZF-1541
• http://framework.zend.com/issues/browse/ZF-7152

This entry was posted on Sunday, June 28th, 2009 at 4:38 pm and is filed under Zend Framework. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.