Force phpMyAdmin to display and save UTF-8

How to solve a problem with phpMyAdmin, where it won't save or display UTF-8 properly.

Working with character sets can be rather complicated, but seems easy enough once you got the basics.

We recently had to deal with a problem, where data submitted from phpMyAdmin, wouldn't display correctly on the site. The reason for this was, that phpMyAdmin, for whatever reason was inserting data in a different character set then the one we used, seems stupid when they talk about that its us who have a problem on their wiki - Garbled data.

However, some nice people posted a fix, or more like a hack, which appears to solve the problem.

Forcing phpMyAdmin to use UTF-8

What you need to do, is to open up mysql.dbi.lib.php, located in libraries/dbi, and add the below code just before return $link; in the PMA_DBI_connect function.

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

The whole function should look like the below when you are done.

function PMA_DBI_connect($user, $password, $is_controluser = false, $server = null, $auxiliary_connection = false)
{
    global $cfg, $php_errormsg;
  
    if ($server) {
        $server_port = (empty($server['port']))
            ? ''
            : ':' . (int)$server['port'];
        $server_socket = (empty($server['socket']))
            ? ''
            : ':' . $server['socket'];
        $server_persistant = (empty($server['persistant']))
            ? false
            : true;
    } else {
	  $server_port   = (empty($cfg['Server']['port']))
                   ? ''
                   : ':' . (int)$cfg['Server']['port'];
	  $server_socket = (empty($cfg['Server']['socket']))
                   ? ''
                   : ':' . $cfg['Server']['socket'];
    }

    if (strtolower($cfg['Server']['connect_type']) == 'tcp') {
        $cfg['Server']['socket'] = '';
    }

    $client_flags = 0;

    // always use CLIENT_LOCAL_FILES as defined in mysql_com.h
    // for the case where the client library was not compiled
    // with --enable-local-infile
    $client_flags |= 128;

    /* Optionally compress connection */
    if (defined('MYSQL_CLIENT_COMPRESS') && $cfg['Server']['compress']) {
        $client_flags |= MYSQL_CLIENT_COMPRESS;
    }

    /* Optionally enable SSL */
    if (defined('MYSQL_CLIENT_SSL') && $cfg['Server']['ssl']) {
        $client_flags |= MYSQL_CLIENT_SSL;
    }
    
    if (!$server) {
        $link = PMA_DBI_real_connect($cfg['Server']['host'] . $server_port . $server_socket, $user, $password, empty($client_flags) ? NULL : $client_flags);

      // Retry with empty password if we're allowed to
        if (empty($link) && $cfg['Server']['nopassword'] && !$is_controluser) {
	        $link = PMA_DBI_real_connect($cfg['Server']['host'] . $server_port . $server_socket, $user, '', empty($client_flags) ? NULL : $client_flags);
        }
    } else {
        if (!isset($server['host'])) {
	        $link = PMA_DBI_real_connect($server_socket, $user, $password, NULL, $server_persistant); 
        } else {
            $link = PMA_DBI_real_connect($server['host'] . $server_port . $server_socket, $user, $password, NULL, $server_persistant);
        }
    }
    if (empty($link)) {
        if ($is_controluser) {
            trigger_error($GLOBALS['strControluserFailed'], E_USER_WARNING);
            return false;
        }
        // we could be calling PMA_DBI_connect() to connect to another
        // server, for example in the Synchronize feature, so do not
        // go back to main login if it fails
        if (! $auxiliary_connection) {
            PMA_log_user($user, 'mysql-denied');
            PMA_auth_fails();
        } else {
            return false;
        }
    } // end if
    if (! $server) {
        PMA_DBI_postConnect($link, $is_controluser);
    }
    mysql_query("SET SESSION CHARACTER_SET_RESULTS =latin1;",$link);
    mysql_query("SET SESSION CHARACTER_SET_CLIENT =latin1;",$link);
    return $link;
}

External Sources

http://www.developpez.net/forums/d105647-2/php/outils/phpmyadmin/utf-8-phpmyadmin-problemes-dencodage/

Comment by magickey

Posted The:13/02/2013 At: 18:53

It is much easier:

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!

Author: magickey