Wordpress - fixing UTF8 database issues

[bad_brain]

a little ago a customer contacted me because his Wordpress site displayed all kind of silly symbols instead of hyphens or apostrophes. which of course messed up the readability a lot.
turned out some of the database tables were using latin_swedish_ci as character set instead of the default utf8...reason: unknown.
when trying to fix it I googled a lot but no real help to find, so here are the queries needed to fix the database by converting it to utf8, and to replace the broken symbols:

Code: Select all

ALTER TABLE wp_posts CONVERT TO CHARACTER SET utf8;
UPDATE wp_posts SET post_content = REPLACE(post_content, '“', '“');
UPDATE wp_posts SET post_content = REPLACE(post_content, '”', '”');
UPDATE wp_posts SET post_content = REPLACE(post_content, '’', '’');
UPDATE wp_posts SET post_content = REPLACE(post_content, '‘', '‘');
UPDATE wp_posts SET post_content = REPLACE(post_content, '—', '–');
UPDATE wp_posts SET post_content = REPLACE(post_content, '–', '—');
UPDATE wp_posts SET post_content = REPLACE(post_content, '•', '-');
UPDATE wp_posts SET post_content = REPLACE(post_content, '…', '…');
UPDATE wp_posts SET post_content = REPLACE(post_content, '  ', ' ');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'Â', '');

of course you can apply it to any database (not only Wordpress) and tables, you simply have to adjust the table names then.

[ph0bYx]

That is something very common in my experience with Wordpress. Every site that I create ``latin1_swedish_ci`` is set as the character set. I doesn't make sense why WP is doing that, it would be more logical for it to default to UTF.

[bad_brain]

hm, yeah, this is pretty odd because actually WP uses UTF8 as default....are you poicking a different language during setup?

but actually it's no real issue as long as ALL tables have the same charset because characters should be all displayed properly then....it just gets annoying when you have tables with different charsets.

[ph0bYx]

Nope, I always download the latest version of WP and use English during the installation.

[bad_brain]

well, MySQL sets up a default database during install where it stores settings, one of them is this one:

Code: Select all

DEFAULT CHARACTER SET latin1

strange thing is that this is taken from my server where it still sets up the tables as utf8....

[l0ngb1t]

am not sure but this should work

Code: Select all

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE db_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

[bad_brain]

am not sure but this should work

Code: Select all

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE db_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

hmmm....would be worth a try....