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

1- Open file:

C:\wamp\bin\mysql\mysql5.5.24\my.ini

2- Look for [mysqld] entry and append:

character-set-server = utf8
skip-character-set-client-handshake

The whole view should look like:

[mysqld]
  port=3306
  character-set-server = utf8
  skip-character-set-client-handshake

3- Restart MySQL service!

Answer 2

ALTER TABLE table_name CONVERT to CHARACTER SET utf8;

*IMPORTANT: Back-up first, execute after

Answer 3

I had the same problem,

Set all text/varchar collations in phpMyAdmin to utf-8 and in php files add this:

mysql_set_charset("utf8", $your_connection_name);

This solved it for me.

Answer 4

Change latin1_swedish_ci to utf8_general_ci in phpmyadmin->table_name->field_name

This is where you find it on the screen:

Answer 5

It works for me,

mysqli_query($con, "SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'");

Answer 6

Here is my way how do I restore the data without looseness from latin1 to utf8:

/**
     * Fixes the data in the database that was inserted into latin1 table using utf8 encoding.
     *
     * DO NOT execute "SET NAMES UTF8" after mysql_connect.
     * Your encoding should be the same as when you firstly inserted the data.
     * In my case I inserted all my utf8 data into LATIN1 tables.
     * The data in tables was like ДЕТСКИÐ.
     * But my page presented the data correctly, without "SET NAMES UTF8" query.
     * But phpmyadmin did not present it correctly.
     * So this is hack how to convert your data to the correct UTF8 format.
     * Execute this code just ONCE!
     * Don't forget to make backup first!
     */
    public function fixIncorrectUtf8DataInsertedByLatinEncoding() {
        // mysql_query("SET NAMES LATIN1") or die(mysql_error()); #uncomment this if you already set UTF8 names somewhere

        // get all tables in the database
        $tables = array();
        $query = mysql_query("SHOW TABLES");
        while ($t = mysql_fetch_row($query)) {
            $tables[] = $t[0];
        }
        // you need to set explicit tables if not all tables in your database are latin1 charset
        // $tables = array('mytable1', 'mytable2', 'mytable3'); # uncomment this if you want to set explicit tables

        // duplicate tables, and copy all data from the original tables to the new tables with correct encoding
        // the hack is that data retrieved in correct format using latin1 names and inserted again utf8
        foreach ($tables as $table)  {
            $temptable = $table . '_temp';
            mysql_query("CREATE TABLE $temptable LIKE $table") or die(mysql_error());
            mysql_query("ALTER TABLE $temptable CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci") or die(mysql_error());
            $query = mysql_query("SELECT * FROM `$table`") or die(mysql_error());
            mysql_query("SET NAMES UTF8") or die(mysql_error());
            while ($row = mysql_fetch_row($query)) {
                $values = implode("', '", $row);
                mysql_query("INSERT INTO `$temptable` VALUES('$values')") or die(mysql_error());
            }
            mysql_query("SET NAMES LATIN1") or die(mysql_error());
        }

        // drop old tables and rename temporary tables
        // this actually should work, but it not, then
        // comment out this lines if this would not work for you and try to rename tables manually with phpmyadmin
        foreach ($tables as $table)  {
            $temptable = $table . '_temp';
            mysql_query("DROP TABLE `$table`") or die(mysql_error());
            mysql_query("ALTER TABLE `$temptable` RENAME `$table`") or die(mysql_error());
        }
        // now you data should be correct

        // change the database character set
        mysql_query("ALTER DATABASE DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci") or die(mysql_error());

        // now you can use "SET NAMES UTF8" in your project and mysql will use corrected data
    }