php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #35183 write into mysql with utf8 not right
Submitted: 2005-11-10 16:36 UTC Modified: 2005-11-12 10:56 UTC
From: lzx21cn at 21cn dot com Assigned:
Status: Not a bug Package: MySQL related
PHP Version: 4.4.0-4 OS: linux debian unstable
Private report: No CVE-ID: None
 [2005-11-10 16:36 UTC] lzx21cn at 21cn dot com
Description:
------------
write in mysql by utf8
it is wrong!

Reproduce code:
---------------
1.modify php.ini
...
mbstring.internal_encoding = UTF-8
mbstring.http_output = UTF-8
mbstring.detect_order = UTF-8

2. php file (utf8)
$sql="insert into test1 values(2,'四大皆空');";
mysql_query($sql);

Expected result:
----------------
*record where id=1 is inputed in gnome-terminal

---- command: mysql IN gnome-terminal (utf8) ----

select * from test1;

id | name
1  | 皆大欢喜
2  | 四大皆空
--------------------------------------------------

Actual result:
--------------
---- command: mysql IN gnome-terminal (utf8) ----

select * from test1;

id | name
1  | 皆大欢喜
2  | (something character which I unknow)
--------------------------------------------------

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-11-10 17:24 UTC] tony2001@php.net
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc.

If possible, make the script source available online and provide
an URL to it here. Try to avoid embedding huge scripts into the report.


 [2005-11-11 16:34 UTC] lzx21cn at 21cn dot com
you need php file (utf8)
----------------------
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<?php
$connect=mysql_connect("localhost","kevin","");

$select=mysql_select_db("kevin",$connect);

$query="insert into test1 values (2,\"四大皆空\");";
$result=mysql_query($query);

$result=mysql_query("select * from test1;");
$rows=mysql_num_rows($result);
for ($i=0;$i<$rows;$i++)
{
    
   $a=mysql_fetch_array($result);
   echo "第一个字段是:".$a[0]." | ";
   echo "第二个字段是:".$a[1]."<br>";
}
mysql_close();
?>
 [2005-11-11 16:41 UTC] lzx21cn at 21cn dot com
sorry, 1st comment file is error

This is you need php file (utf8)
----------------------
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<?php
$connect=mysql_connect("localhost","kevin","");

$select=mysql_select_db("kevin",$connect);

$query="insert into test1 values (2,\"四大皆空\");";
$result=mysql_query($query);

$result=mysql_query("select * from test1;");
mysql_close();
?>
 [2005-11-11 18:24 UTC] tony2001@php.net
Try to do the same without PHP (using the console interface of MySQL) and see if it works this way.
If it does, put the code somewhere in the Net and paste the link here, so the Unicode data won't be corrupted by the bug system.
Also add the expected and actual results, I doubt I can guess them myself.
And try to explain why do you think it's a problem of PHP, not MySQL or something else (I'd be glad if you try to use something close to English to do that).
 [2005-11-12 02:03 UTC] lzx21cn at 21cn dot com
do the same without PHP (using the console interface of MySQL in   gnome-terminal
-----------------------------------------------------------
drop table test1;
create table test1(id int,name char(4));
insert into test1 values(1,'皆大欢喜');
insert into test1 values(2,'四大皆空');
select * from test1;
id | name
1  | 皆大欢喜
2  | 四大皆空
-------------------------------------------
the reslut is good!

-------------------------------------------------
Why do I think it's a problem of PHP, not MySQL

1st. read/write by console vs. by php

by console: the result is right
by php    : the result is wrong,write four Chinese character and read only one chinese character back,and other character lost.

2nd. read Mysql by java vs. by php (creat record by console)
by java: the result is right
by php : the result is ? at field 'name'
--------------------------------------------------
 [2005-11-12 02:42 UTC] lzx21cn at 21cn dot com
The problem maybe "Data truncation"  When php write to mysql
but I do not know resolve it
 [2005-11-12 07:17 UTC] georg@php.net
can't repeat (tested with MySQL 4.1 and 5.0):

<?php
$connect=mysql_connect("localhost","phptest","php");

$select=mysql_select_db("test",$connect);

/* create test table */
mysql_query("DROP TABLE IF EXISTS test_35183");
mysql_query("CREATE TABLE test_35183 (a varchar(20) character set utf8)");

/* set client connection to utf8 */
mysql_query("SET NAMES utf8");

$expected = "&#22235;&#22823;&#30342;&#31354;";

$query="insert into test1 values ('$expected')";
$result=mysql_query($query);

if (($result=mysql_query("select a from test_35183")))
{
    $row=mysql_fetch_row($result);
    printf("Expected: %s  Returned: %s\n", $expected, $row[0]);
}

mysql_query("DROP TABLE test_35183");
mysql_free_result($result);
mysql_close();
?>

Output:
Expected: &#22235;&#22823;&#30342;&#31354;  Returned: &#22235;&#22823;&#30342;&#31354;
 [2005-11-12 09:33 UTC] tony2001@php.net
Try to build PHP using official sources and the latest release.
Please tell us the version of MySQL you're using.
 [2005-11-12 10:54 UTC] lzx21cn at 21cn dot com
php file must inclue this line

<?php
/* set client connection to utf8 */
mysql_query("SET NAMES utf8");
?>

Thank you very much!
 [2005-11-12 10:56 UTC] tony2001@php.net
No PHP bug -> bogus.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Mar 28 10:01:26 2024 UTC