Page 1 of 1

How to retrieve UT8_BIN data from MYSQL in PHP

Posted: Wed Apr 21, 2010 5:46 am
by sanchitgupta
Hi,

I am saving some UT8 characters(example : किसान) in mysql database. Now when I retrieve that I just get boxes. What should I do to retrieve it in correct form.

Please help !

Thanks

-Sanchit

Re: How to retrieve UT8_BIN bata from MYSQL in PHP

Posted: Wed Apr 21, 2010 6:59 am
by Eran
You need to set the connection character set to UTF8 as well.
http://dev.mysql.com/doc/refman/5.0/en/ ... ction.html

Re: How to retrieve UT8_BIN bata from MYSQL in PHP

Posted: Wed Apr 21, 2010 3:41 pm
by sanchitgupta
I have actually done that! I am able to store UTF8 strings in mysql databse. I have problems in retrieving the String in UTF8 format in PHP file!
pytrin wrote:You need to set the conenction character set to UTF8 as well.
http://dev.mysql.com/doc/refman/5.0/en/ ... ction.html

Re: How to retrieve UT8_BIN bata from MYSQL in PHP

Posted: Wed Apr 21, 2010 4:43 pm
by Eran
I have actually done that!
What is "that"? are you running a query such as "SET NAMES UTF8" before retrieving data? did you set your default connection collation to UTF and disabled client handshake? I don't think you did what you should be doing since you are getting the results you reported.

Re: How to retrieve UT8_BIN bata from MYSQL in PHP

Posted: Thu Apr 22, 2010 7:03 am
by sanchitgupta
Thanks pytrin for replying. I have done "SET NAMES UT8" and default connection to UT8

Following is my table.
CREATE TABLE IF NOT EXISTS `user` (
`userid` int(11) NOT NULL AUTO_INCREMENT,
`password` varchar(120) NOT NULL,
`name` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`occupation` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`place` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`lastaccess` date DEFAULT NULL,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
I dont know how to disable client handshake. Kindly help me! I am a beginner in php and sql :?
pytrin wrote:
I have actually done that!
What is "that"? are you running a query such as "SET NAMES UTF8" before retrieving data? did you set your default connection collation to UTF and disabled client handshake? I don't think you did what you should be doing since you are getting the results you reported.

Re: How to retrieve UT8_BIN data from MYSQL in PHP

Posted: Thu Apr 22, 2010 7:39 am
by Eran
check out this thread - viewtopic.php?f=1&t=105682&p=564207

Re: How to retrieve UT8_BIN data from MYSQL in PHP

Posted: Thu Apr 22, 2010 12:25 pm
by sanchitgupta
The problem in that post seems to be similar to mine!

Following are the steps I followed, but still question marks are displayed instead of desired UTF string :(
1. Enable UT8 encoding in mysql (see my last reply for table)
2. I pasted
skip-character-set-client-handshake
in php.ini file as I am using xampp.
3. Following is php file which I am using to retrieve name

Code: Select all

<?php
include_once("db_connection.php");
$query="Select * from user where userid=1";
$r = mysql_query($query);
$row = mysql_fetch_assoc($r);
$value = $row["name"];
echo $value;
mysql_close($db)
?>
pytrin wrote:check out this thread - viewtopic.php?f=1&t=105682&p=564207

Re: How to retrieve UT8_BIN bata from MYSQL in PHP

Posted: Thu Apr 22, 2010 4:58 pm
by John Cartwright
Hint: Keep reading the following over and over. :)
pytrin wrote: are you running a query such as "SET NAMES UTF8" before retrieving data?

Re: How to retrieve UT8_BIN bata from MYSQL in PHP

Posted: Fri Apr 23, 2010 7:51 am
by sanchitgupta
Havent I already done that? Please see the table.
John Cartwright wrote:Hint: Keep reading the following over and over. :)
pytrin wrote: are you running a query such as "SET NAMES UTF8" before retrieving data?

Re: How to retrieve UT8_BIN data from MYSQL in PHP

Posted: Fri Apr 23, 2010 8:12 am
by Eran
SET NAMES is a query you run to control the connection character set. It has nothing to do with the table schema. Please read again the previously attached link, all the information you need is there and on the thread I linked to.