Geek Answers Handbook

Use utf8 or not - MySQL and PHP character encoding problem

I have a string stored in MySQL like this: یکی از Ø where is my character set of utf8 table and sort utf8_general_ci .

When I retrieve a row from MySQL, the browser displays it as follows: یکی از بهترین راه and this is normal (it is Persian).

Note:

  • I used mysql_query("SET NAMES 'utf8_persian_ci'"); after connecting to MySQL.

  • I put the <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> at the top of each page.

Now I need to use mysql_query("SET NAMES 'utf8'"); .

But after using it, the browser displays the following line: یکی از Ø (the same as in MySQL).

How can I modify saved MySQL records and solve my problem? Or use some PHP code to convert the output encoding?

+4
source share
3 answers

Your problem is that your SET NAMES 'utf8_persian_ci' command is invalid (utf8_persion_ci is sorting, not encoding). If you run it in the terminal, you will see the Unknown character set: 'utf8_persian_ci' error message. So your application, when it was storing data, used the latin1 character latin1 . MySQL interpreted your entry as latin1 characters, which it then saved as utf-8. Similarly, when the data was discarded, MySQL converted it from UTF-8 back to latin1 and (hopefully most of the time) the original bytes that you gave it.

In other words, all your data in the database is completely confused, but it just worked out that way.

To fix this, you need to undo what you have done. The easiest way is to use PHP:

  • SET NAMES latin1;
  • Select each text box from each table.
  • SET NAMES utf8;
  • Update the same rows using the same row without changes.

Alternatively, you can perform these steps inside MySQL, but this is difficult because MySQL understands that the data is in a specific character set. You need to change the text columns to a BLOB type, and then change them back to text types using the utf8 character set. See the section below ALTER TABLE MySQL Documentation with a Warning in red .

After performing any of these operations, the bytes stored in the database columns will be the actual character set that they claim to be. Then make sure you always use mysql_set_charset('utf8') for any database access with PHP that you can do in the future! Otherwise, you will ruin things again. (Note: do not use simple mysql_query('SET NAMES utf8') ! There are corner cases (for example, a reset connection) where it can be reset to latin1 without your knowledge. mysql_set_charset() will set the encoding when necessary.)

It would be better if you switched from mysql_* functions and instead used PDO with the parameter charset=utf8 in PDO dsn .

+4
source

You probably need to change the Charset and Collation columns.

Try this request:

 ALTER TABLE `YOUR_TABLE_NAME` CHANGE `YOUR_COLUMN_NAME` `YOUR_COLUMN_NAME` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL

(In the example, I assumed that VARCHAR is NULL DEFAULT VALUE)

If this works for you, you can paste the code below into your my.conf file to make utf8 the default encoding:

 default-character-set = utf8
+1
source

Possible solutions:

  • Add "AddDefaultCharset UTF-8" to your .htaccess file for your site.
  • Add a header ('Content-Type: text / html; charset = utf-8'); at the top of your php file.
  • Use the utf8_encode () php function for data to be displayed in Persian.
+1
source

More articles:


All Articles