Funny characters in text after server move

I have just moved a MODX Revo website (2.2.9) from one server to another, backed up the files and Database, restored them both and updated the config files - all is now working perfectly except for the fact that characters such as - and £ have all been replaced with a ?. I have checked the character set of both the original database and the new database and they are the same utf8. Any ideas as to why this may have happened? Many thanks

Your new database does not have the same character set and collation as the old one. Check the tables, and see what the collation is for the varchar and other text fields.

Aaah, yes, Sorry Susan, i have just read your other posts on this one - checked the character set but not the collation. MODX allows you to specify the collation when you install. If you are moving to a different server and creating the database yourself before you import your SQL backup it defaults the collation to latin1_Swedish_ci. How do i go about changing this in phpMyAdmin, before i import the SQL backup?

In phpMyAdmin, go to the Operations tab, then choose the appropriate Collation.

Hi Susan, thanks for that. I changed the collation to utf_general_Ci in the new database i created, which is what is specified during the original MODX install, but when the SQL backup is imported it the collation still states latin1_swedish_ci - any ideas as to what i am doing wrong?

Sounds like your original DB had that collation. What does it say in the backup .sql file?

Hi Susan, Yes you are right, which is very odd! looking at the original tables in mysql using the command 'show table status;', it would appear that the tables have the collation latin1_swedish_ci, even though i am sure i set it to utc8_general_ci during installation. The SQL backup file itself though states it as latin1 (does this mean the same as latin1_swedish_ci?).

Any ideas on what is the best thing for me to do then, to ensure that the characters are not all converted to ?, there are hundreds of resources affected by this? The original database had latin1_swedish_ci as the correlation and the new one has the same, yet the characters are being converted to ?

Thanks for your time

Setting the various character set variables in MySQL (PhpMyAdmin) is a black art. I've actually had extras create custom tables in latin1_swedish_ci even though my entire DB and the MODX setting are for UTF-8.

You might try the utility snippet here, but back everything up first: http://bobsguides.com/convert-db-utf8.html

Thanks BobRay. It just seems odd - in the SQL backup itself the characters are as they should be - i would have thought there was some way to import that data as it stands in the backup files, without the files being converted to ?, especially if the correlation was the same in the old and new databases?

Yes, you'd think so.