phpmyadmin display utf8

By ET

Took me a whole afternoon to figure it out.

MySQL used non-utf-8 encoding, so when I use phpmyadmin to interpret it, the returned result is messed up.

mysql> show variables like ‘character_set%’;
+————————–+——————————–+
| 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 | \mysql\share\charsets\ |
+————————–+——————————–+
8 rows in set (0.00 sec)

mysql> show variables like ‘collation%’;
+———————-+——————-+
| Variable_name | Value |
+———————-+——————-+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_general_ci |
| collation_server | latin1_general_ci |
+———————-+——————-+
3 rows in set (0.00 sec)

I wrote a program to query the DB and used the following code to check if the characters are indeed utf-8:

use strict;
use Encode;
my $iftest=1;
my $ifpause=1;
use DBI;
my  $conn = DBI->connect     ("DBI:mysql:ki","","")     or die("Cannot connect: $DBI::errstr");
$conn->{RaiseError} = 0;
my $sql;
my $query;
my $row;
my $id=1;
$sql=qq(select * from categorylinks );
$query = $conn->prepare($sql);
$query->execute 		or print("n[Select ID]Error executing SQL statement! $DBI::errstrn");
while ($row = $query->fetchrow_arrayref){ 		my $line=@$row[1];
$line=decode("utf8",$line);
$line=encode("gbk",$line);
print "$linen";
}  $conn->disconnect;
exit;

Turned out that the output are correct.

By phpmyadmin still outputs messy code for Chinese, so I modified the following file

/phpMyAdmin/libraries/select_lang.lib.php

Under “// MySQL charsets ma”
Changed from
‘utf-8? => ‘utf8?,
To
‘utf-8? => ‘latin1?,
Voila, it worked

====Update 2009-09-09======

The above solution worked on Windows and Mac, but did not work on Ubuntu. Here is what I need to do on Ubuntu:

This is a hack to make phpmyadmin show correct multibyte characters (Russian, Japanese, etc) in browse and edit view.
Courtesy: Oyama @ irc.freenode.net##php

=========================================================================
cd /usr/share/phpmyadmin/libraries/dbi/      (or wherever is your phpmyadmin directory)

edit “mysql.dbi.lib.php”
locate the end of the function “function PMA_DBI_connect($user, $password, $is_controluser = FALSE)”     (around line 88 for me)
insert after “PMA_DBI_postConnect($link, $is_controluser);” and before “return $link;” the following two lines:
“mysql_query(“SET SESSION CHARACTER_SET_RESULTS =latin1;”,$link);”
“mysql_query(“SET SESSION CHARACTER_SET_CLIENT =latin1;”,$link);”
save and quit

edit “mysqli.dbi.lib.php”
locate the end of the function “function PMA_DBI_connect($user, $password, $is_controluser = false)”     (around line 115 for me)
insert after “PMA_DBI_postConnect($link, $is_controluser);” and before “return $link;” the following two lines:
“mysqli_query($link, “SET SESSION CHARACTER_SET_RESULTS =latin1;”);”
“mysqli_query($link, “SET SESSION CHARACTER_SET_CLIENT =latin1;”);”
save and quit

That’s it!
Still, i don’t really consider it as a real solution, since it just “avoids” the real problem and doesn’t correct it.
Anyway, it works, and that’s all i need ;)

25 Responses to “phpmyadmin display utf8”

  1. cory Says:

    This worked for me. Great find, thanks.

  2. ET Says:

    Cool. :-)

  3. Markus Says:

    Thanks, this was a solution for my problem too!

  4. aw tan Says:

    if the purpose is just for phpmyadmin to display chinese utf8 chars correctly, you can set charset to utf8 either at db, table, or column level, such as

    create table foo (name varchar(255) set charset utf-8); OR

    create table foo (name varchar(255)) set charset utf-8

    I have tried it. phpmyadmin does display the chinese characters correctly.

    I have a different problem — those utf8 chars don’t show up correctly in php applications.

  5. Piotras Says:

    Thank you. It works for lithuanian language too.

  6. HP Says:

    Many thanks, this solved my problem!

  7. a man Says:

    wow it work! Thanks a lot.
    ——————————————–
    but I am confused.

    ‘utf-8′ => ‘utf8′, It’s correct,but messed up the display result.
    ‘utf-8′ => ‘latin1′ It’s is uncorrect,but fixed the problem.

    WHAT THE HELL WAS THAT

  8. ET Says:

    It has to do with the internal storage of the characters. I guess this works as a quick fix, it does not mean the database is configured properly.

  9. Antonio Says:

    Thank you very much. I was going crazy with this. I spent hours trying to fix this. Thank you very much.

  10. Ioannis Says:

    WoW! It does work indeed!!!

    All the encodings (on the php, database, table and collations level) are correct (utf8) but the result in the phpmyadmin (phpMyAdmin – 2.11.4) are not displayed correctly. When ‘utf-8? => ‘latin1? it fixed the display problem (Note: the web page encoding still correctly is utf-8 – Strange).
    Does anyone knows why is this happening ? Should we report it as a bug ?

    Thanx all

  11. Mulle Says:

    This worked for me too. It’s really tricky to locate the problem when there are so many parts involved. When I realized that it was phpMyAdmin that messed up I found this quick fix.
    Thanks a lot!

  12. Sergey Says:

    Much thanks!

  13. dingle Says:

    I found a fix somewhere else. Suppose all your tables are utf8, in your php code, right after mysql_connect(), you need to add mysql_query(“set names utf8″), which basically means all future incoming queries are encoded in utf8. Right after this command in mysql command line, i can see those chinese chars are encoded wrongly. This means you have to change all your data to utf8 (i suppose phpmyadmin is using utf8 for queries). YMMV. ref: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

  14. dingle Says:

    A more “correct” fix. After connecting to database, “SET NAMES utf8″ to set utf8 encoding for all incoming queries. Unfortunately, it means that all your previous data are encoded incorrectly (the default “NAMES” must be latin1). Phpmyadmin is correctly using utf8 to query myql database.
    It works for me, YMMV. More details plz see Ref:
    http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

  15. simsik Says:

    thanks, it works, i was going crazy with russian and estonian

  16. Herman Santos Says:

    You Rock!

    I always use it to export DBs

  17. When you thought the DB was UTF-8 but it wasn’t « OmegaDelta Says:

    [...] In the mysqli.dbi.lib.php and mysql.dbi.lib.php (two files), in the PMA_DBI_connect function, add the code (thanks to Mike Zhang for the code): [...]

  18. William Denniss Says:

    I had this same problem.

    My data was not being stored correctly (though my PHP app could use it through a latin1 connection correctly).

    How I fixed my data was to apply your hack to phpMyAdmin (thanks!), then export my data (so it’s now correct UTF-8), remove the hack, and re-import the data. Then set my connection in PHP to utf-8.

    Full writeup on my blog: http://omegadelta.net/2010/11/23/when-you-thought-the-db-was-utf-8-but-it-wasnt/

  19. duc Says:

    Thanks for the tip. I have been searching for a quick solution and this seems to work.

  20. development Says:

    development…

    [...]NullVoid » phpmyadmin display utf8[...]…

  21. appliance repair Newport Beach Says:

    Some really interesting details you have written on blog.mikezhang.com .Aided me a lot, just what I was searching for :D .

  22. http://tinyurl.com/emptlong29531 Says:

    I actually desired to show this unique blog, “NullVoid

  23. magickey Says:

    1- Edit file:
    my.ini (MsWindos)
    my.cnf (GNU/linux)

    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!

  24. buy dvds online Says:

    Very interesting info !Perfect just what I was searching for! “Energy is the power that drives every human being. It is not lost by exertion by maintained by it.” by Germaine Greer.

  25. NothingCtrl Says:

    Latest version of PhpMyAdmin i don’t see the file mysql.dbi.lib.php :(

Leave a Reply


BlogTimer
You are visitor number several since September 1, 2001

Copyright Xiaoquan (Michael) Zhang, 2004-2020. All rights reserved.
All trademarks property of their owners.