How to retrieve UT8_BIN data from MYSQL in PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
sanchitgupta
Forum Newbie
Posts: 5
Joined: Wed Apr 21, 2010 5:40 am

How to retrieve UT8_BIN data from MYSQL in PHP

Post 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
Last edited by sanchitgupta on Thu Apr 22, 2010 6:56 am, edited 1 time in total.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How to retrieve UT8_BIN bata from MYSQL in PHP

Post 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
Last edited by Eran on Wed Apr 21, 2010 4:44 pm, edited 1 time in total.
sanchitgupta
Forum Newbie
Posts: 5
Joined: Wed Apr 21, 2010 5:40 am

Re: How to retrieve UT8_BIN bata from MYSQL in PHP

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How to retrieve UT8_BIN bata from MYSQL in PHP

Post 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.
sanchitgupta
Forum Newbie
Posts: 5
Joined: Wed Apr 21, 2010 5:40 am

Re: How to retrieve UT8_BIN bata from MYSQL in PHP

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How to retrieve UT8_BIN data from MYSQL in PHP

Post by Eran »

check out this thread - viewtopic.php?f=1&t=105682&p=564207
sanchitgupta
Forum Newbie
Posts: 5
Joined: Wed Apr 21, 2010 5:40 am

Re: How to retrieve UT8_BIN data from MYSQL in PHP

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: How to retrieve UT8_BIN bata from MYSQL in PHP

Post 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?
sanchitgupta
Forum Newbie
Posts: 5
Joined: Wed Apr 21, 2010 5:40 am

Re: How to retrieve UT8_BIN bata from MYSQL in PHP

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: How to retrieve UT8_BIN data from MYSQL in PHP

Post 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.
Post Reply