I messed up the settings with W3 Total Cache (tried to import all the media to my library, didn’t work out well, broke all my links to every picture).
So I took my latest backup of the database, copy/paste the _post and _postmeta tables inside my phpmyadmin.
It brought back the links and pictures as expected, but now all the french characters (à,é,è etc) are not displayed properly.
I took the backup from the plugin WP-DBManager, which doesn’t seem to handle UTF-8 properly.
What’s the fastest way to correct the issue ?
Edited for more details:
The SQL backup header is
DROP TABLE IF EXISTS `hojd_posts`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8;
However I have those characters badly encoded coming directly in my sql commands (eg: franÃ§ais for “français”)…
You might be able to solve this if you have a text editor with good encoding support. That way, you could switch between the Latin 1 and the UTF-8 encoding until you have the right combination. I use SubEthaEdit which can convert but also reinterpret a file when you change the encoding.
ç should be encoded as
c3 a7 in UTF-8 when you view them as bytes. What could be happening here is that the file was interpreted as Latin 1 first, where
c3 a7 means
Ã§, and then saved as UTF-8, where
Ã§ is saved as
c3 83 c2 a7. You want the
c3 a7 version.
The way to get back to a nice
ç is to open the file as UTF-8, save it as Latin-1, and then open it again as if it was UTF-8.
Once you did this, you can import the file into MySQL, but specify it is UTF-8, otherwise MySQL might try to interpret it as Latin 1 and you will still have the
I had a similiar problem with german umlauts. The problem also occurred on a db export with phpmyadmin. My solution was to export the database with the the command line tool mysql and the parameter “–default-character-set=”utf8”.
Another approach, if you haven’t access to a linux shell:
Install and use MySQLDumper
A common problem with older WordPress databases and even newer ones is that the database tables get set as latin-1 but the contents are actually encoded as UTF-8. If you try to export as UTF-8 MySQL will attempt to convert the (supposedly) Latin-1 data to UTF-8 resulting in double encoded characters since the data was already UTF-8.
The solution is to export the tables as latin-1. Since MySQL thinks they are already latin-1 it will do a straight export.
Since the dumped data was not converted during the export process, it’s actually UTF-8 encoded data.
Create your new table as UTF-8 If your CREATE TABLE command is in your SQL dump file, change the character set from ‘latin1′ to ‘utf8′.
Import your data normally. Since you’ve got UTF-8 encoded data in your dump file, the declared character set in the dump file is now UTF-8, and the table you’re importing into is UTF-8, everything will go smoothly.