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,'database');
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);

[sinasalek]

Gergely Temesi <tgergely@freemail.hu> send an email to me about this problem, and he noticed some important things.
this problem solved with Mark <mw@ANGRYLanfear.com> 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