The situation:

You have a MySQL database in a non-English language which works fine through web based apps. Because the encoding issues were not taken care of properly from the first place, when you want to view the records using phpMyAdmin (in xampp), you see garbage characters, like these:

مرکز آموزش توپخانه نزاجا

If you edit the records in phpMyAdmin, you can add non-English characters and they look fine, only the existing data is incorrectly displayed.

When you open the .MYI file (one of the three files of the MySQL database) in a Unicode capable viewer like Notepad++ & choose UTF-8 as encoding, you can see most of your data sitting there with correct representation.

Exporting to .sql does no good for you.

The default character set & character encoding are ‘latin1’ & ‘latin1_swedish_ci’

The configuration (in my case):

xampp 1.8.2 (Apache 2.4.4, MySQL 5.5.32, PHP 5.4.16, phpMyAdmin 4.0.4), win 7 x64

Possible fixes:

You can try the following fixes:

1) Changing whatever you see to utf8_general_ci, including: database collation (through operations), table collation (through operations), columns collation, & server connection collation (in phpMyAdmin home page, general settings section)

2) Changing these server variables to utf8 (through phpMyAdmin home page): character set client, character set connection, character set database, character set results, character set server & character set system.

3) Changing these server variables to utf8_unicode_ci (through phpMyAdmin home page): collation connection, collation database & collation server.

4) Adding this line:

@MySQL_Query("SET NAMES utf8");

to xampp\php\pear\MDB2\Driver\mysql.php after this line:

$connection = @call_user_func_array($connect_function, $params);

5) Adding these 3 lines to my.ini:

collation_server=utf8_unicode_ci
character_set_server=utf8
skip-character-set-client-handshake

6) Adding these 2 lines:

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

to xampp\phpMyAdmin\libraries\dbi\mysql.dbi.lib.php, below the following line:

PMA_DBI_postConnect($link, $is_controluser);

7) Changing this:

'utf-8' => 'utf8',

to this:

'utf-8' => 'latin1',

in xampp\phpMyAdmin\libraries\select_lang.lib.php

8) This solution is what I used myself. As the records were fine in web based interfaces, I used a piece of php code to retrieve all of the records in comma separated values (CSV format), and then it’s possible to do many things with them (including importing them back into MySQL database with correct encoding settings). php code looked something like this (where ‘person’ is the table I’m working on):

.
.
.
$qp = mysql_query("select * from `person`");
while($fp = mysql_fetch_array($qp))
{
echo $fp['field_1'] . ',';
echo $fp['field_2'] . ',';
.
.
.
echo $fp['field_n-1'] . ',';
echo $fp['field_n'] ;
echo "<br>";
}
.
.
.

The output should look something like this:

record_1.field_1,record_1.field_2,…,record_1.field_n-1,record_1.field_n
record_2.field_1,record_2.field_2,…,record_2.field_n-1,record_2.field_n
.
.
.
record_n-1.field_1,record_n-1.field_2,…,record_n-1.field_n-1,record_n.field_n
record_n.field_1,record_n.field_2,…,record_n.field_n-1,record_n.field_n

Additional notes:

• There’s a MySQL odbc driver connector which after installing enables you to import databases from MySQL into Microsoft Excel.
• There’re programs to connect to MySQL, like HeidiSQL.
• There’re programs to read MySQL files (.MYI .MYD .frm) like ‘xbaseview pro’ (which also reads other formats)