Zend_Db: utf8 query string gets inserted as non-utf8

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.

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)


One Comment Trackback URL | Comments RSS

  1. Mat Dodgson Says:

    You are my hero! I just spent an hour trying to track this problem down.

    The utf-8 gets double encoded if you don’t set the charset for zend_db to utf-8. So you see the utf-8 escape characters in places like mysql workbench, and in my browser output. Then I grab it and paste it into another program and it gets automatically converted so it looks fine!

    Thanks so much for this tip!

One Trackback

  1. May 26, 2011 : UTF-8 – Zend_Db e PDO « Andre Santos

Post a Comment

Your email is never published nor shared. You're allow to say what you want...

Warning: fsockopen() [function.fsockopen]: php_network_getaddresses: getaddrinfo failed: Name or service not known in /home/remydamo/websites/qc4blog/www/wp-content/plugins/sweetcaptcha-revolutionary-free-captcha-service/library/sweetcaptcha.php on line 81

Warning: fsockopen() [function.fsockopen]: unable to connect to www.sweetcaptcha.com:80 (php_network_getaddresses: getaddrinfo failed: Name or service not known) in /home/remydamo/websites/qc4blog/www/wp-content/plugins/sweetcaptcha-revolutionary-free-captcha-service/library/sweetcaptcha.php on line 81