Inserting UTF-8 Japanese into MySQL: phpMyAdmin works, my form doesn't.

[Richard Pavonarius]

Using MySQL 4.1.3-beta-standard, PHP Version 4.4.6. I've also tried
PHP Version 5.2.2 with exactly the same results.

I have a table with utf8_general_ci collation. If I use phpMyAdmin, I
can insert Japanese text into it with no problem.

However, a simple form I've created is inserting mangled Japanese text
into MySQL.

My form's page has a meta tag setting the charset to UTF-8.
After the form has been submitted, I can echo the Japanese text to the
feedback page just fine, so I assume the data is being sent properly
encoded.

高橋アントニオ gets put into MySQL from my form as 高æ(c)‹ã‚¢ãƒ³ãƒˆãƒ‹ã‚ª
But as I said, I can insert the same text via phpMyAdmin without any problem.

The php code is as simple as I can make it

$username = 'tony';
$password = 'emmauspa';
$name = '高橋アントニオ';
$email = 'me@...';
$gender = 'M';
$country = '4';
$nationality = '4';
$language = '1';
$dob = '1994-01-04';
$phone = '967-1261';
$connection = mysql_connect('localhost', 'user', 'password') or die
('Unable to connect!');
mysql_select_db('gdbase') or die ('Unable to select database!');
$query="INSERT INTO members (username, password, name, email, gender,
country, nationality, language, dob, phone)
VALUES('$username','$password','$name','$email','$gender',$country,$nationality,$language,'$dob','$phone')";
$result = mysql_query($query) or die ("Error in query: $query. " .
mysql_error());
echo 'New record inserted with ID' . mysql_insert_id() . '<br />';
echo mysql_affected_rows() . 'records(s) affected.';
mysql_close($connection);

My mbstring settings in php.ini:

mbstring.detect_order = auto
mbstring.encoding_translation = On
mbstring.func_overload = 0
mbstring.http_input = auto
mbstring.http_output = UTF-8
mbstring.internal_encoding = UTF-8
mbstring.language = Neutral
mbstring.substitute_character = no value

What the heck am I doing wrong, or how can I figure out where the
problem is? I tried to follow the insertion routine in phpMyAdmin, but
the code is way over my head. I've been at this for days and days.

Thanks,

Rich

[Wil Clouser]

It looks like your string is getting UTF encoded twice.  If MySQL
doesn't think the incoming data is in UTF-8, it'll encode it for you
(possibly duplicating it).  You can check out the character sets in
MySQL by running (from your app - not the cli):

SHOW VARIABLES LIKE 'char%';

To test if my theory is correct, run this command before your queries:

SET NAMES 'utf8';

That will tell MySQL that the incoming data is utf8.  If your string
works after your run that query, you should change the default
character set in your .conf file.  More info here:
http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

Wil

On 5/13/07, Richard Pavonarius <rich.pav@...> wrote:

> Using MySQL 4.1.3-beta-standard, PHP Version 4.4.6. I've also tried
> PHP Version 5.2.2 with exactly the same results.
>
> I have a table with utf8_general_ci collation. If I use phpMyAdmin, I
> can insert Japanese text into it with no problem.
>
> However, a simple form I've created is inserting mangled Japanese text
> into MySQL.
>
> My form's page has a meta tag setting the charset to UTF-8.
> After the form has been submitted, I can echo the Japanese text to the
> feedback page just fine, so I assume the data is being sent properly
> encoded.
>
> 高橋アントニオ gets put into MySQL from my form as 高æ(c)‹ã‚¢ãƒ³ãƒˆãƒ‹ã‚ª
> But as I said, I can insert the same text via phpMyAdmin without any problem.
>
> The php code is as simple as I can make it
>
> $username = 'tony';
> $password = 'emmauspa';
> $name = '高橋アントニオ';
> $email = 'me@...';
> $gender = 'M';
> $country = '4';
> $nationality = '4';
> $language = '1';
> $dob = '1994-01-04';
> $phone = '967-1261';
> $connection = mysql_connect('localhost', 'user', 'password') or die
> ('Unable to connect!');
> mysql_select_db('gdbase') or die ('Unable to select database!');
> $query="INSERT INTO members (username, password, name, email, gender,
> country, nationality, language, dob, phone)
> VALUES('$username','$password','$name','$email','$gender',$country,$nationality,$language,'$dob','$phone')";
> $result = mysql_query($query) or die ("Error in query: $query. " .
> mysql_error());
> echo 'New record inserted with ID' . mysql_insert_id() . '<br />';
> echo mysql_affected_rows() . 'records(s) affected.';
> mysql_close($connection);
>
> My mbstring settings in php.ini:
>
> mbstring.detect_order   = auto
> mbstring.encoding_translation   = On
> mbstring.func_overload  = 0
> mbstring.http_input     = auto
> mbstring.http_output    = UTF-8
> mbstring.internal_encoding      = UTF-8
> mbstring.language = Neutral
> mbstring.substitute_character   = no value
>
> What the heck am I doing wrong, or how can I figure out where the
> problem is? I tried to follow the insertion routine in phpMyAdmin, but
> the code is way over my head. I've been at this for days and days.
>
> Thanks,
>
> Rich
>