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

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...)

Answer 2

I had exactly the same problem. Database charset is utf-8 and collation is utf8_unicode_ci. I was able to see Unicode text in my webapp but the phpMyAdmin and sqldump results were garbled.

It turned out that the problem was in the way my web application was connecting to MySQL. I was missing the encoding flag.

After I fixed it, I was able to see Greek characters correctly in both phpMyAdmin and sqldump but lost all my previous entries.

Answer 3

Its realy simple to add multilanguage in myphpadmin if you got garbdata showing in myphpadmin, just go to myphpadmin click your database go to operations tab in operation tab page see collation section set it to utf8_general_ci, after that all your garbdata will show correctly. a simple and easy trick

Answer 4

First, from the client do

mysql> SHOW VARIABLES LIKE 'character_set%';

This will give you something like

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     | 
| character_set_connection | latin1                     | 
| character_set_database   | latin1                     | 
| character_set_filesystem | binary                     | 
| character_set_results    | latin1                     | 
| character_set_server     | latin1                     | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
+--------------------------+----------------------------+

where you can inspect the general settings for the client, connection, database

Then you should also inspect the columns from which you are retrieving data with

SHOW CREATE TABLE TableName

and inspecting the charset and collation of CHAR fields (though usually people do not set them explicitly, but it is possible to give CHAR[(length)] [CHARACTER SET charset_name] [COLLATE collation_name] in CREATE TABLE foo ADD COLUMN foo CHAR ...)

I believe that I have listed all relevant settings on the side of mysql. If still getting lost read fine docs and perhaps this question which might shed some light (especially how I though I got it right by looking only at mysql client in the first go).

Answer 5

phpmyadmin doesn't follow the MySQL connection because it defines its proper collation in phpmyadmin config file.

So if we don't want or if we can't access server parameters, we should just force it to send results in a different format (encoding) compatible with client i.e. phpmyadmin

for example if both the MySQL connection collation and the MySQL charset are utf8 but phpmyadmin is ISO, we should just add this one before any select query sent to the MYSQL via phpmyadmin :

SET SESSION CHARACTER_SET_RESULTS =latin1;

Answer 6

I did a little more googling and came across this page

The command doesn't seem to make sense but I tried it anyway:

In the file /usr/share/phpmyadmin/libraries/dbi/mysqli.dbi.lib.php at the end of function PMA_DBI_connect() just before the return statement I added:

mysqli_query($link, "SET SESSION CHARACTER_SET_RESULTS =latin1;");
mysqli_query($link, "SET SESSION CHARACTER_SET_CLIENT =latin1;");

And it works! I now see Japanese characters in phpMyAdmin. WTF? Why does this work?