0

phpMyAdmin insists on forcing my utf-8 characters into latin1.

Server Info:

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.0.27 - Source distribution
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)

phpMyAdmin Version information: 4.0.10 PHP Version 5.2.11

The server version and PHP version is not under my control, and phpMyAdmin is the latest version that will run under PHP 5.2

Neither Import or straight SQL will work. My own internal PHP tools handle utf8 just fine, and update the database correctly.

DROP TABLE IF EXISTS `tempchar`;
SET character_set_client = utf8;
CREATE TABLE `tempchar` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `active` int(1) NOT NULL DEFAULT '0',
  `type` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `tempchar` VALUES (382,'Vicenté Marcos de Cervantés',2,0);

The end result (viewed as utf8) is:

382 : Vicent� Marcos de Cervant�s

Same result when switched to 'Western' (latin1 etc)

382 : Vicenté Marcos de Cervantés

Code that produces the above output:

$result=mysql_query("select * from tempchar where active=2 order by name");
while($row=mysql_fetch_array($result)) {
  echo "$row[id] : $row[name]<br>\n";
}

It is clearly phpMyAdmin stepping in and doing something unnecessary. Has anyone got any clues as to how to get it to behave?

Robbie Matthews
  • 1,404
  • 14
  • 22
  • How exactly are you viewing the result? You mean in phpMyAdmin? – deceze Jun 18 '14 at 08:22
  • That is the contents of the table displayed in a dirt-simple html page. – Robbie Matthews Jun 18 '14 at 22:09
  • Not a duplicate. The problem is specifically manifested in phpMyAdmin while importing, and nowhere else in my system. – Robbie Matthews Jun 19 '14 at 09:02
  • Does the data appear correctly in phpMyAdmin itself? Not your "dirt-simple html page", but the phpMyAdmin interface. What exactly does your "dirt-simple html page" do? – deceze Jun 19 '14 at 09:52
  • Hmmm... the problem MAY be between PHP and Mysql... further investigation is required. When I pull the data I inserted via PHP, it is coming back as utf8 as expected... but binary() dumps are telling something else. – Robbie Matthews Jun 20 '14 at 00:32
  • I retract my statement: The answers *were* in that post, but not obviously so. – Robbie Matthews Jun 20 '14 at 00:47
  • Given the limitations of the versions if PHP and MySql I have to work with, mysql_set_charset('utf8') was the answer. It's important to note that using 'utf-8' (note the dash) does not work and in fact silently fails. The mysql library was helpfully converting from latin1 to utf8 and back again, in a hard-to-spot way. In debugging this, the mysql function 'binary()' helped distinguish what was in the database as compared to what was being presented to the program. The sql command "show variables like '%char%'" also helped. – Robbie Matthews Jun 20 '14 at 00:56
  • You may also want to read this: http://kunststube.net/frontback – deceze Jun 20 '14 at 05:41

0 Answers0