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: ÃƒÂ®
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.
I've had to try to 'fix' a number of UTF8 broken situations in the past, and unfortunately it's never easy, and often rather impossible.
Unless you can determine exactly how it was broken, and it was always broken in that exact same way, then it's going to be hard to 'undo' the damage.
If you want to try to undo the damage, your best bet would be to start writing some sample code, where you attempt numerous variations on calls to mb_convert_encoding() to see if you can find a combination of 'from' and 'to' that fixes your data. In the end, it's often best to not even bother worrying about fixing the old data because of the pain levels involved, but instead to just fix things going forward.
However, before doing this, you need to make sure that you fix everything that is causing this issue in the first place. You've already mentioned that your DB table collation and editors are set properly. But there are more places where you need to check to make sure that everything is properly UTF-8:
If you miss up on any one step through your whole process, the encoding can be mangled and problems arise. Once you get in the 'groove' of doing utf-8 though, this all becomes second nature. And of course, PHP6 is supposed to be fully unicode complaint from the getgo, which will make lots of this easier (hopefully)
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.
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.