0

I recently upgraded my site to PHP 5.7 and a new install of the MySQL database. The previous data was imported.

Now I have a lot of the question marks in diamonds in outputted text. I have read up, and the problem seems to be that the collation of my database is set to latin1_swedish_ci and I need to be utf8 to correctly render special characters stored in the database.

If I change the collation will this potentially solve my problem, fixing the older postings?

Do I switch off the site before changing the collation, and does it require a reboot to take effect? I'm a little nervous about corrupting the data as my users would be extremely upset to lose their historical postings.

I am not setting up a new database, I've upgraded an existing database, and need to keep the historical data.

I followed one of the answers posted below and it worked for data that was inputted into the database before the upgrade, but now renders newer data with various “ in place of apostrophes, etc.

D Rice
  • 71
  • 9
  • *"Now I have a lot of the question marks in diamonds in outputted text."* - so check the file's encoding, and if that still doesn't work, pass UTF-8 before querying – Funk Forty Niner Apr 25 '16 at 18:13
  • 2
    see also http://stackoverflow.com/questions/279170/utf-8-all-the-way-through – Funk Forty Niner Apr 25 '16 at 18:15
  • 1
    if you're worried about corrupting data, just copy that db in a tmp table – Funk Forty Niner Apr 25 '16 at 18:19
  • You can also check this out http://stackoverflow.com/questions/1294117/how-to-change-collation-of-database-table-column – Edward Apr 25 '16 at 18:28
  • Thank you - the solution was very simple after all! I used mysql_query('SET NAMEs utf8') and that solved the historical issue. – D Rice Apr 25 '16 at 18:29
  • Unfortunately that does fix the historical posts, but breaks the newer posts (since the upgrade). I'm looking for a fix that will work for the historical data as well as the new data. – D Rice Apr 25 '16 at 18:52

1 Answers1

1

You can use

$con = mysqli_connect("HOST","DB_USER","PASSWORD","DB_NAME");
$db->set_charset('utf8');

OR

mysql_query('SET NAMES utf8');
Autor69
  • 104
  • 5