How to display UTF-8 characters in phpMyAdmin?

Question

I have my database properly set to UTF-8 and am dealing with a database containing Japanese characters. If I do SELECT *... from the mysql command line, I properly see the Japanese characters. When pulling data out of the database and displaying it on a webpage, I see it properly.

However, when viewing the table data in phpMyAdmin, I just see garbage text. ie.

ç§ã¯æ—¥æœ¬æ–™ç†ãŒå¥½ãã§ã™ã€‚日本料ç†ã‚...

How can I get phpMyAdmin to display the characters in Japanese?

The character encoding on the HTML page is set to UTF-8.

Edit:

I have tried an export of my database and opened up the .sql file in geany. The characters are still garbled even though the encoding is set to UTF-8. (However, doing a mysqldump of the database also shows garbled characters).

The character set is set correctly for the database and all tables ('latin' is not found anywhere in the file)

CREATE DATABASE `japanese` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

I have added the lines to my.cnf and restarted mysql but there is no change. I am using Zend Framework to insert data into the database.

I am going to open a bounty for this question as I really want to figure this out.

Answer 1

Unfortunately, phpMyAdmin is one of the first php application that talk to MySQL about charset correctly. Your problem is most likely due to the fact that the database does not store the correct UTF-8 strings at first place.

In order to correctly display the characters correctly in phpMyAdmin, the data must be correctly stored in the database. However, convert the database into correct charset often breaks web apps that does not aware charset-related feature provided by MySQL.

May I ask: is MySQL > version 4.1? What web app is the database for? phpBB? Was the database migrated from an older version of the web app, or an older version of MySQL?

My suggestion is not to brother if the web app you are using is too old and not supported. Only convert database to real UTF-8 if you are sure the web app can read them correctly.

---

Edit:

Your MySQL is > 4.1, that means it's charset-aware. What's the charset collation settings for you database? I am pretty sure you are using latin1, which is MySQL name for ASCII, to store the UTF-8 text in 'bytes', into the database.

For charset-insensitive clients (i.e. mysql-cli and php-mod-mysql), characters get displayed correctly since they are being transfer to/from database as bytes. In phpMyAdmin, bytes get read and displayed as ASCII characters, that's the garbage text you seem.

Countless hours had been spend years ago (2005?) when MySQL 4.0 went obsolete, in many parts of Asia. There is a standard way to deal with your problem and gobbled data:

1. Back up your database as .sql
2. Open it up in UTF-8 capable text editor, make sure they look correct.
3. Look for charset collation latin1_general_ci, replace latin1 to utf8.
4. Save as a new sql file, do not overwrite your backup
5. Import the new file, they will now look correctly in phpMyAdmin, and Japanese on your web app will become question marks. That's normal.
6. For your php web app that rely on php-mod-mysql, insert mysql_query("SET NAMES UTF8"); after mysql_connect(), now the question marks will be gone.

7. Add the following configuration my.ini for mysql-cli:

   # CLIENT SECTION [mysql] default-character-set=utf8 # SERVER SECTION [mysqld] default-character-set=utf8 

For more information about charset on MySQL, please refer to manual: http://dev.mysql.com/doc/refman/5.0/en/charset-server.html

Note that I assume your web app is using php-mod-mysql to connect to the database (hence the mysql_connect() function), since php-mod-mysql is the only extension I can think of that still trigger the problem TO THIS DAY.

phpMyAdmin use php-mod-mysqli to connect to MySQL. I never learned how to use it because switch to frameworks* to develop my php projects. I strongly encourage you do that too.

• Many frameworks, e.g. CodeIgniter, Zend, use mysqli or pdo to connect to databases. mod-mysql functions are considered obsolete cause performance and scalability issue. Also, you do not want to tie your project to a specific type of database.

Answer 2

If you're using PDO don't forget to initiate it with UTF8:

 $con = new PDO('mysql:host=' . $server . ';dbname=' . $db . ';charset=UTF8', $user, $pass,  array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")); 

(just spent 5 hours to figure this out, hope it will save someone precious time...)