473,574 Members | 3,123 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem With MySQL 4.1.x and Unicode (UTF8)

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("SE T CHARACTER SET utf8");

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

$link=mysqli_co nnect('localhos t','root',null, 'database');
mysqli_query($l ink, "SET CHARACTER SET utf8");
if ($result = mysqli_query($l ink, "SELECT name_farsi FROM icons order by
name_farsi"))
{
$charset = mysqli_characte r_set_name();
while ($row = mysqli_fetch_ro w($result)) {
echo("$row[0]"."<br>");
}
mysqli_free_res ult($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);

Jul 17 '05 #1
4 5038
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("SE T 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($li nk, "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.
Jul 17 '05 #2
hi,
many thanks for your help.
i tested your suggestion. but my problem not solved.
My CODE :
ini_set('mbstri ng.internal_enc oding','UTF-8');
ini_set('mbstri ng.http_input', 'UTF-8');
ini_set('mbstri ng.http_output' ,'UTF-8');
ini_set('mbstri ng.internal_enc oding','UTF-8');

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

mysqli_free_res ult($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)

Jul 17 '05 #3
Gergely Temesi <tg******@freem ail.hu> send an email to me about this
problem, and he noticed some important things.
this problem solved with Mark <mw@ANGRYLanfea r.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('mbstri ng.internal_enc oding','UTF-8');
ini_set('mbstri ng.http_input', 'UTF-8');
ini_set('mbstri ng.http_output' ,'UTF-8');

$link=mysqli_co nnect('localhos t','root',null, 'test');
//set character sets
mysqli_query($l ink, "SET CHARACTER SET utf8");
mysqli_query($l ink, "SET NAMES 'utf8'");

//insert a persian word to "name|farsi " field.
mysqli_query($l ink,"INSERT INTO `test` ( `name|farsi`) VALUES ('utf8
string-->تست')");
//print records
if ($result = mysqli_query($l ink, "SELECT `name|farsi` FROM test order by
`name|farsi`"))
{
while ($row = mysqli_fetch_ro w($result))
echo $row[0]."<br>";
mysqli_free_res ult($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

Jul 17 '05 #4
Gergely Temesi <tg******@freem ail.hu> send an email to me about this
problem, and he noticed some important things.
this problem solved with Mark <mw@ANGRYLanfea r.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('mbstri ng.internal_enc oding','UTF-8');
ini_set('mbstri ng.http_input', 'UTF-8');
ini_set('mbstri ng.http_output' ,'UTF-8');

$link=mysqli_co nnect('localhos t','root',null, 'test');
//set character sets
mysqli_query($l ink, "SET CHARACTER SET utf8");
mysqli_query($l ink, "SET NAMES 'utf8'");

//insert a persian word to "name|farsi " field.
mysqli_query($l ink,"INSERT INTO `test` ( `name|farsi`) VALUES ('utf8
string-->تست')");
//print records
if ($result = mysqli_query($l ink, "SELECT `name|farsi` FROM test order by
`name|farsi`"))
{
while ($row = mysqli_fetch_ro w($result))
echo $row[0]."<br>";
mysqli_free_res ult($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

Jul 17 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
9525
by: Aditya Ivaturi | last post by:
We have a CMS which is written is based on php & mysql. Recently we received a request to support multiple languages so that sites in that particular laguage can be created. I did some search on the google and it seems I have to build in multibyte support for php and mysql. Mbstring (http://us3.php.net/mbstring) claims to support multiple...
8
5259
by: Bill Eldridge | last post by:
I'm trying to grab a document off the Web and toss it into a MySQL database, but I keep running into the various encoding problems with Unicode (that aren't a problem for me with GB2312, BIG 5, etc.) What I'd like is something as simple as: CREATE TABLE junk (junklet VARCHAR(2500) CHARACTER SET UTF8)); import MySQLdb, re,urllib
0
1297
by: Stormblade | last post by:
Hey all, I have an existing JSP web application which retrieved data from a SQLServer database and displayed it. The data contained Unicode chars. This worked fine. I changed databases/JDBC drivers over to MySQL. I've set the database to use utf8 and the tables as well. I've viewed the data in the table and it contains unicode just fine,...
15
2411
by: Peter Afonin | last post by:
Hello, I'm struggling with the string conversion to MD5 which I've never user before. I have a string that I need to encode which looks approximately like this: "pva:0.05:101214:pa7735tH:inv_desc=205308:shp_Email=petera_gudzon.net:lang =ru:shp_PaymentNo=20040825205308:shp_UserID=pva:shp_Price=2.95:shp_HostPlan= BU:shp_Term=2"
18
34100
by: Ger | last post by:
I have not been able to find a simple, straight forward Unicode to ASCII string conversion function in VB.Net. Is that because such a function does not exists or do I overlook it? I found Encoding.Convert, but that needs byte arrays. Thanks, /Ger
1
1510
by: Marcus | last post by:
Hello, I recently upgraded from 4.0 to 4.1. I've spent the last few days reading up on charsets since I never really thought about it when I used 4.0. I thought I had most of it figured out, but then I saw this in phpMyAdmin and am as confused as when I started: MySQL charset: UTF-8 Unicode (utf8) MySQL connection collation:...
8
2005
by: Daz | last post by:
Hi everyone. I was faced with the choice of whether my problem is indeed a PHP problem or a MySQL. I have decided it's a PHP problem as I don't experience the same problem when I execute the same query at the CLI. I am having trouble executing a large query through my PHP script. It takes about 7-11 seconds on average to execute, whereas...
3
2871
by: Robin Becker | last post by:
I am seeing different outcomes from simple requests against a common database when run from a freebsd machine and a win32 box. The test script is ####################### import MySQLdb, sys print sys.version print MySQLdb.__version__ db=MySQLdb.connect(host='appx',db='sc_0',user='user',passwd='secret',use_unicode=True)
1
5809
by: erikcw | last post by:
Hi, I'm trying to insert some data from an XML file into MySQL. However, while importing one of the files, I got this error: Traceback (most recent call last): File "wa.py", line 304, in ? main() File "wa.py", line 257, in main curHandler.walkData()
0
7842
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, well explore What is ONU, What Is Router, ONU & Routers main...
0
7764
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8110
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8273
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7862
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6514
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5658
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3775
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1375
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.