Problem With MySQL 4.1.x and Unicode (UTF8)

[sinasalek]

i have a problem with MySQL 4.1.x and UTF8.
in version 4.0, i'm using html forms with utf8 charset for inserting
unicode strings. but in version 4.1.x it is not working! if i change the
charset of column,

ALTER TABLE `icons` CHANGE `name_farsi` `name_farsi` VARCHAR( 99 )
CHARACTER SET utf8 COLLATE utf8_persian_ci DEFAULT NULL

and change default charset of database like below code :

mysql_query("SET CHARACTER SET utf8");

-i see somthing like below (the strings in that columns are in utf8
format) :

$link=mysqli_connect('localhost','root',null,'data base');
mysqli_query($link, "SET CHARACTER SET utf8");
if ($result = mysqli_query($link, "SELECT name_farsi FROM icons order by
name_farsi"))
{
$charset = mysqli_character_set_name();
while ($row = mysqli_fetch_row($result)) {
echo("$row[0]"."<br>");
}
mysqli_free_result($result);
}
-----result-----
بیمارستان
راهنمای تور
تورها
کشتی رانی
فرودگاه

you can see the last line of above result, it's correct, becuse i inserted
this record with phpMyAdmin v2.6.0. but the other result as you see, are
not readable.

when i trying to insert a record with below way :
INSERT INTO `icons` ( `name_farsi`) VALUES
('سلام');
-----result----
???
ÓáÇã
بیمارستان
راهنمای تور
تورها
کشتی رانی
فرودگاه
as you see, the first line is not readable. if i convert that value to
utf8 with utf8_encode(), i will see somthing between first result and end
result!
if i does not set default char set of ("SET CHARACTER SET utf8"), the
result is :
???
بیمارستان
راهنمای تور
تورها
کشتی رانی
???????

but this results , does not sort correctly! it is like that i'm using
MYSQL 4.0 not MySQL 4.1.x
how can i solve this problem?!
please help me.
-----------------------------
-- Table structure for table `icons`
--

CREATE TABLE `icons` (
`id` int(6) unsigned NOT NULL auto_increment,
`name` varchar(100) character set latin1 default NULL,
`name_farsi` varchar(100) character set utf8 collate utf8_persian_ci
default NULL,
`filename` varchar(100) character set latin1 default NULL,
`sort_number` int(100) default NULL,
`comment` varchar(255) character set latin1 default NULL,
`comment_farsi` varchar(255) character set latin1 default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `icons` VALUES (3, 'Airport',
'فرودگاه', '3d
icon/airport.gif', NULL, NULL, NULL);
INSERT INTO `icons` VALUES (4, 'Tour Guide', 'تورها',
'icon/eghamati.gif', NULL, NULL, NULL);
INSERT INTO `icons` VALUES (5, 'Hospital',
'بیمارستان', 'bimarestan.gif', NULL, NULL,
NULL);
INSERT INTO `icons` VALUES (6, 'Tours', '?', 'toor.gif', NULL, NULL,
NULL);
INSERT INTO `icons` VALUES (7, 'Ships', '?', 'kashtirani.gif', NULL, NULL,
NULL);

[Mark]

sinasalek wrote:

i have a problem with MySQL 4.1.x and UTF8.
in version 4.0, i'm using html forms with utf8 charset for inserting
unicode strings. but in version 4.1.x it is not working! if i change the
charset of column,

ALTER TABLE `icons` CHANGE `name_farsi` `name_farsi` VARCHAR( 99 )
CHARACTER SET utf8 COLLATE utf8_persian_ci DEFAULT NULL

and change default charset of database like below code :

mysql_query("SET CHARACTER SET utf8");

[el snip-o-rama!]

I ran into the exact same problem some weeks back, and I've found that,
for MySQL 4.1.x, x > 2, you have to use the following command to get the
connection working properly:

$mysqli->query("SET NAMES 'utf8'");

which I suppose would be:

mysql_query($link, "SET NAMES 'utf8'");

there are four places you have to worry about charset:

1. in your PHP script (set mbstring in PHP.ini to Unicode)
2. in your database (looks like you got that covered)
3. in the CONNECTION to your database (SET NAMES 'utf8')
4. in the output to the client browser.
good ruck!

mark.


--
I am not an ANGRY man. Remove the rage from my email to reply.

[sinasalek]

hi,
many thanks for your help.
i tested your suggestion. but my problem not solved.
My CODE :
ini_set('mbstring.internal_encoding','UTF-8');
ini_set('mbstring.http_input','UTF-8');
ini_set('mbstring.http_output','UTF-8');
ini_set('mbstring.internal_encoding','UTF-8');

$link=mysqli_connect('localhost','root',null,'data base');
//set character sets
mysqli_query($link, "SET CHARACTER SET utf8");
mysqli_query($link, "SET NAMES 'utf8'");
//encdoe and then insert a persian work to "name_farsi" field.
mysqli_query($link, "INSERT INTO `icons` ( `name_farsi`) VALUES
('".utf8_encode('بهار')."');");
//print records
if ($result = mysqli_query($link, "SELECT name_farsi FROM icons order by
name_farsi"))
{
while ($row = mysqli_fetch_row($result)) {
echo $row[0]."<br>";
}

mysqli_free_result($result);
}
------------------------------------------------------------------------------------------------------------------------
i really confused and i don't know where is the problem!!
you told me "I ran into the exact same problem some weeks back", can you
tell me, what was your problem,exactly? please.
or can you give me, part of your code, like above code?
or is there any forum that you were write about your problem?
or is there any article about this problem?
i will be very glad , if you answer my questions.
(sorry for my bad english, and my strange questions)

[sinasalek]

Gergely Temesi <tg******@freemail.hu> send an email to me about this
problem, and he noticed some important things.
this problem solved with Mark <[e-mail]> and Gergely help.

Mark's solution :
1. in your PHP script (set mbstring in PHP.ini to Unicode)
2. in your database (looks like you got that covered)
3. in the CONNECTION to your database (SET NAMES 'utf8')
4. in the output to the client browser.

Gergely wrote to me :
He(Mark) missed only one thing: your text editor has to be set to UTF-8!
For example in WinXP NotePad -> File -> Save as… -> Character coding
-> UTF-8 (… or something like this in your own language).

below example can explain solution clearly :
---------------------------------------------------------------------------------------------
I added Gergely's solution to Mark's solution, and problem solved.
here is my code (php file that contain below code is encoded by utf8) :

<?php
//this below three lines are not necessary, i tested without them and
everything worked correctly.
ini_set('mbstring.internal_encoding','UTF-8');
ini_set('mbstring.http_input','UTF-8');
ini_set('mbstring.http_output','UTF-8');

$link=mysqli_connect('localhost','root',null,'test ');
//set character sets
mysqli_query($link, "SET CHARACTER SET utf8");
mysqli_query($link, "SET NAMES 'utf8'");

//insert a persian word to "name|farsi" field.
mysqli_query($link,"INSERT INTO `test` ( `name|farsi`) VALUES ('utf8
string-->تست')");
//print records
if ($result = mysqli_query($link, "SELECT `name|farsi` FROM test order by
`name|farsi`"))
{
while ($row = mysqli_fetch_row($result))
echo $row[0]."<br>";
mysqli_free_result($result);
}
?>

unbelievable, problem was my PHP Editor !!!!, i'm using Zend Studio and
this PHP editor can't handle utf8!
as Gergely told i tested with notepad , and i could insert a record with a
UTF8 field easily and correctly!
---------------------------------------------------------------------------------------------
good luck friends

[sinasalek]

Gergely Temesi <tg******@freemail.hu> send an email to me about this
problem, and he noticed some important things.
this problem solved with Mark <[e-mail]> and Gergely help.

Mark's solution :
1. in your PHP script (set mbstring in PHP.ini to Unicode)
2. in your database (looks like you got that covered)
3. in the CONNECTION to your database (SET NAMES 'utf8')
4. in the output to the client browser.

Gergely wrote to me :
He(Mark) missed only one thing: your text editor has to be set to UTF-8!
For example in WinXP NotePad -> File -> Save as… -> Character coding
-> UTF-8 (… or something like this in your own language).

below example can explain solution clearly :
---------------------------------------------------------------------------------------------
I added Gergely's solution to Mark's solution, and problem solved.
here is my code (php file that contain below code is encoded by utf8) :

<?php
//this below three lines are not necessary, i tested without them and
everything worked correctly.
ini_set('mbstring.internal_encoding','UTF-8');
ini_set('mbstring.http_input','UTF-8');
ini_set('mbstring.http_output','UTF-8');

$link=mysqli_connect('localhost','root',null,'test ');
//set character sets
mysqli_query($link, "SET CHARACTER SET utf8");
mysqli_query($link, "SET NAMES 'utf8'");

//insert a persian word to "name|farsi" field.
mysqli_query($link,"INSERT INTO `test` ( `name|farsi`) VALUES ('utf8
string-->تست')");
//print records
if ($result = mysqli_query($link, "SELECT `name|farsi` FROM test order by
`name|farsi`"))
{
while ($row = mysqli_fetch_row($result))
echo $row[0]."<br>";
mysqli_free_result($result);
}
?>

unbelievable, problem was my PHP Editor !!!!, i'm using Zend Studio and
this PHP editor can't handle utf8!
as Gergely told i tested with notepad , and i could insert a record with a
UTF8 field easily and correctly!
---------------------------------------------------------------------------------------------
good luck friends