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 
May 1st, 2007 at 11:18 pm
This worked for me. Great find, thanks.
May 3rd, 2007 at 2:47 pm
Cool.
November 29th, 2007 at 8:03 am
Thanks, this was a solution for my problem too!
December 25th, 2007 at 1:05 am
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.
May 5th, 2008 at 5:37 am
Thank you. It works for lithuanian language too.
May 7th, 2008 at 6:33 pm
Many thanks, this solved my problem!
May 8th, 2008 at 9:52 am
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
May 8th, 2008 at 5:29 pm
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.
August 20th, 2008 at 4:38 pm
Thank you very much. I was going crazy with this. I spent hours trying to fix this. Thank you very much.
September 14th, 2008 at 4:49 pm
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
December 7th, 2008 at 5:05 am
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!
June 22nd, 2009 at 4:46 pm
Much thanks!
June 24th, 2009 at 7:53 am
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
June 24th, 2009 at 7:59 am
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
February 12th, 2010 at 4:59 am
thanks, it works, i was going crazy with russian and estonian
February 13th, 2010 at 6:42 am
You Rock!
I always use it to export DBs
November 23rd, 2010 at 12:14 pm
[...] 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): [...]
November 23rd, 2010 at 12:20 pm
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/
April 4th, 2011 at 8:42 am
Thanks for the tip. I have been searching for a quick solution and this seems to work.
November 26th, 2011 at 12:56 am
development…
[...]NullVoid » phpmyadmin display utf8[...]…
December 14th, 2011 at 7:14 pm
Some really interesting details you have written on blog.mikezhang.com .Aided me a lot, just what I was searching for
.
January 27th, 2013 at 11:43 am
I actually desired to show this unique blog, “NullVoid
February 14th, 2013 at 2:01 am
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!