Random Characters in Pages and Posts – Change Database Collation

If you are getting random characters in pages and posts it might be related to running database tables with a different collation to that of the database as a whole often caused by importing a database from an older server.

There are a lot of scripts out there that can sort this out but this is the one that worked for me:

<?php

// Configuration Section
$server = 'localhost'; 
$username = 'your_username';
$password = 'your_password';
$database = 'your_database';
$new_charset = 'utf8'; 
$new_collation = 'utf8_general_ci'; 

// Connect to database
$db = mysql_connect($server, $username, $password); if(!$db) die("Cannot connect to database server -".mysql_error());
$select_db = mysql_select_db($database); if (!$select_db) die("could not select $database: ".mysql_error());

// change database collation
mysql_query("ALTER DATABASE $database DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");

// Loop through all tables changing collation
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
$table = $tables[0];
mysql_query("ALTER TABLE $table DEFAULT CHARACTER SET $new_charset COLLATE $new_collation");

// loop through each column changing collation
$columns = mysql_query("SHOW FULL COLUMNS FROM $table where collation is not null");
while($cols = mysql_fetch_array($columns)) {
$column = $cols[0];
$type = $cols[1];
mysql_query("ALTER TABLE $table MODIFY $column $type CHARACTER SET $new_charset COLLATE $new_collation");
}

print "changed collation of $table to $new_collation<br/>";
}
print '<br/>The collation of your database has been successfully changed!<br/>';
?>

Edit the database connection details to suit and run the script.

Original script can be found here:

http://www.holisticsystems.co.uk/blog/changing-collation-all-tables-mysql-database

NOTE: This does not clear any existing strange characters from your database which will need to be found and replaced using this query as a starting point:

UPDATE wp_posts SET post_content = REPLACE (post_content, 'AA?&', 'a');

Adapt this to you needs.