"to use utf8 or not - mysql and php character encoding issue" Code Answer


your problem is that your set names 'utf8_persian_ci' command was invalid (utf8_persion_ci is a collation, not an encoding). if you run it in a terminal you will see an error unknown character set: 'utf8_persian_ci'. thus your application, when it stored the data, was using the latin1 character set. mysql interpreted your input as latin1 characters which it then stored encoded as utf-8. likewise when the data was pulled back out, mysql converted it from utf-8 back to latin1 and (hopefully, most of the time) the original bytes you gave it.

in other words, all your data in the database is completely messed up, but it just so happened to work.

to fix this, you need to undo what you did. the most straightforward way is using php:

  1. set names latin1;
  2. select every single text field from every table.
  3. set names utf8;
  4. update the same rows using the same string unaltered.

alternatively you can perform these steps inside mysql, but it's tricky because mysql understands the data to be in a certain character set. you need to modify your text columns to a blob type, then modify them back to text types with a utf8 character set. see the section at the bottom of the alter table mysql documentation labeled "warning" in red.

after you do either one of these things, the bytes stored in your database columns will be the actual character set they claim to be. then, make sure you always use mysql_set_charset('utf8') on any database access from php that you may do in the future! otherwise you will mess things up again. (note, do not use a simple mysql_query('set names utf8')! there are corner cases (such as a reset connection) where this can be reset to latin1 without your knowledge. mysql_set_charset() will set the charset whenever necessary.)

it would be best if you switched away from mysql_* functions and used pdo instead with the charset=utf8 parameter in your pdo dsn.

By Patrick Walton on May 21 2022

Answers related to “to use utf8 or not - mysql and php character encoding issue”

Only authorized users can answer the Search term. Please sign in first, or register a free account.