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

User · Answer

just uncomment this lines in libraries database interface lib php  if    empty  GLOBALS  collation connection                 PMA DBI query  SET CHARACTER SET  utf8      link  PMA DBI QUERY STORE            PMA DBI query  SET collation connection                 PMA sqlAddslashes  GLOBALS  collation connection             link  PMA DBI QUERY STORE     else            PMA DBI query  SET NAMES  utf8  COLLATE  utf8 general ci      link  PMA DBI QUERY STORE        if you store data in utf8 without storing charset you do not need phpmyadmin to re-convert again the connection  This will work

User · Answer

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

User · Answer

the solution for this can be as easy as     find the phpmysqladmin connection function method add this after database is conncted  db conect- gt set charset  utf8

User · Answer

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    Back up your database as  sql Open it up in UTF-8 capable text editor  make sure they look correct  Look for charset collation latin1 general ci  replace latin1 to utf8  Save as a new sql file  do not overwrite your backup Import the new file  they will now look correctly in phpMyAdmin  and Japanese on your web app will become question marks  That s normal  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  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

User · Answer

Change latin1 swedish ci to utf8 general ci in phpmyadmin- table name- field name  This is where you find it on the screen

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt   SET NAMES utf8        just spent 5 hours to figure this out  hope it will save someone precious time

User · Answer

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

User · Answer

First  from the client do  mysql gt  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

User · Answer

ALTER TABLE table name CONVERT to CHARACTER SET utf8    IMPORTANT  Back-up first  execute after

User · Answer

Add     mysql query  SET NAMES UTF8      below   mysql select db   your database name

User · Answer

Easier solution for wamp is      go to phpMyAdmin      click localhost      select latin1 bin for Server connection collation      then start to create database and table

User · Answer

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

User · Answer

The function and file names don t match those in newer versions of phpMyAdmin  Here is how to fix in the newer PHPMyAdmins      Find file   phpmyadmin libraries DatabaseInterface php In function  public function query Right after the opening   add this   if  link    null       mysqli query  link   SET SESSION CHARACTER SET RESULTS  latin1         mysqli query  link   SET SESSION CHARACTER SET CLIENT  latin1          That s it  Works like a charm

[mysql] How to display UTF-8 characters in phpMyAdmin?

Examples related to mysql

Examples related to unicode

Examples related to utf-8

Examples related to phpmyadmin