Fixing broken UTF-8 encoding

Question

I am in the process of fixing some bad UTF-8 encoding. I am currently using PHP 5 and MySQL.

In my database I have a few instances of bad encodings that print like: î

• The database collation is utf8_general_ci
• PHP is using a proper UTF-8 header
• Notepad++ is set to use UTF-8 without BOM
• database management is handled in phpMyAdmin
• not all cases of accented characters are broken

I need some sort of function that will help me map the instances of î, í, ü and others like it to their proper accented UTF-8 characters.

Answer 1

If you have double-encoded UTF8 characters (various smart quotes, dashes, apostrophe ’, quotation mark “, etc), in mysql you can dump the data, then read it back in to fix the broken encoding.

Like this:

mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
    --skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql

mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
    --default-character-set=utf8 DB_NAME < DB_NAME-dump.sql

This was a 100% fix for my double encoded UTF-8.

Source: http://blog.hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/