Fixing "Incorrect string value" Errors in MySQL and PHP

Fixing "Incorrect string value" Errors in MySQL and PHP

When developing a multi-lingual web site, you may run into a situation when string data containing non-latin characters is not saved correctly to the database or the string data gets truncated. For example, recently we've had an interesting case. A string consisting of all Unicode characters was saved correctly while a string containing even a single accented character (e.g. Brésil which is Brazil in French) was causing the error. To fix this, follow these simple steps:

1. Right after mysql_select_db("mydatabase") command in PHP use the following command
mysql_query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");

2. Use the utf8_encode() PHP function. For example in MySQL command may look like this:

$name = utf8_encode("Brésil");
mysql_query("UPDATE countries SET name='$name'");

3. Explicitly set your web pages' encoding

<meta content="text/html; charset=utf-8" http-equiv="Content-Type"></meta>

We hope these quick tips save you a lot of time.