How do I allow foreign characters through php to the database 2

kzn · Nov 8, 2012

Hi,

I have changed the table to use utf-8, and from the mysql prompt I can do an insert and the foreign characters show up exactly how they should ... but now when I use my form and php to insert the same character it comes up with strange letters and characters. I have added the following line in the header of the page <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

Does anyone know how I can solve this... thanks in advance.

jpadie · Nov 9, 2012

the first thing is to make sure that all the transports are using the same character set. so your page should use utf-8, form tags should set to utf-8, your database should use utf-8, the tables should use utf-8 and, most importantly, the connection to the database should use utf-8.

Code:

mysql_connect($host, $user, $pass);
mysql_selectdb($database);
mysql_set_charset('utf-8');

DonQuichote · Nov 9, 2012

kzn said:
I have added the following line in the header of the page <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

This will require the browser to read the file first, then hack the encoding out of it, and re-encode it. Putting the encoding in the encoded file is like keeping the key to a safe inside it. A better way is to use an http header (before any output is sent):

PHP:

header('Content-Type: text/html; charset=utf-8');

This way, the client knows what encoding the file is before it actually arrives. If this encoding is the default for the entire server, you also may consider putting the encoding in php.ini.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

kzn · Nov 9, 2012

Hi Thanks guys

I have tried and failed ... have a look at the below
<?php
header ('Content-Type: text/html; charset=UTF-8');
define('db_user','root');
define('db_password','test123');
define('db_host','localhost');
define('db_name','mydb');

// make the connection
$dbc =@mysql_connect(db_host,db_user,db_password) or die('Could not connect to mysql');

@mysql_select_db(db_name) or die('could not select database');
mysql_set_charset('utf-8');
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"

http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="

http://www.w3.org/1999/xhtml"

xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>Unicode in PHP</title>
</head>
<body style="font-size: 18pt;">
<em>Names from Around the World</em>
<?php
$query = "insert into people (firstname,lastname) values ('João', 'Γιώργος'),('Anton', 'Tomáš'),('Kamilė', 'Frančiška'),('愛子', '杰西卡')";

$result = mysql_query($query);

?>
</body>
</html>

I cant change the database itself because its hosted but I have set the table to use utf-8
CREATE TABLE `people` (
`userid` int(10) unsigned NOT NULL auto_increment,
`firstname` varchar(100) NOT NULL,
`lastname` varchar(100) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

Please help, not sure what else to do.

Thanks,

Below is a print out when I do mysql> SHOW CHARACTER SET;

Charset Description Default collation Maxlen
big5 Big5 Traditional Chinese big5_chinese_ci 2
dec8 DEC West European dec8_swedish_ci 1
cp850 DOS West European cp850_general_ci 1
hp8 HP West European hp8_english_ci 1
koi8r KOI8-R Relcom Russian koi8r_general_ci 1
latin1 cp1252 West European latin1_swedish_ci 1
latin2 ISO 8859-2 Central European latin2_general_ci 1
swe7 7bit Swedish swe7_swedish_ci 1
ascii US ASCII ascii_general_ci 1
ujis EUC-JP Japanese ujis_japanese_ci 3
sjis Shift-JIS Japanese sjis_japanese_ci 2
hebrew ISO 8859-8 Hebrew hebrew_general_ci 1
tis620 TIS620 Thai tis620_thai_ci 1
euckr EUC-KR Korean euckr_korean_ci 2
koi8u KOI8-U Ukrainian koi8u_general_ci 1
gb2312 GB2312 Simplified Chinese gb2312_chinese_ci 2
greek ISO 8859-7 Greek greek_general_ci 1
cp1250 Windows Central European cp1250_general_ci 1
gbk GBK Simplified Chinese gbk_chinese_ci 2
latin5 ISO 8859-9 Turkish latin5_turkish_ci 1
armscii8 ARMSCII-8 Armenian armscii8_general_ci 1
utf8 UTF-8 Unicode utf8_general_ci 3
ucs2 UCS-2 Unicode ucs2_general_ci 2
cp866 DOS Russian cp866_general_ci 1
keybcs2 DOS Kamenicky Czech-Slovak keybcs2_general_ci 1
macce Mac Central European macce_general_ci 1
macroman Mac West European macroman_general_ci 1
cp852 DOS Central European cp852_general_ci 1
latin7 ISO 8859-13 Baltic latin7_general_ci 1
cp1251 Windows Cyrillic cp1251_general_ci 1
cp1256 Windows Arabic cp1256_general_ci 1
cp1257 Windows Baltic cp1257_general_ci 1
binary Binary pseudo charset binary 1
geostd8 GEOSTD8 Georgian geostd8_general_ci 1
cp932 SJIS for Windows Japanese cp932_japanese_ci 2
eucjpms UJIS for Windows Japanese eucjpms_japanese_ci 3

kzn · Nov 9, 2012

Hi

This is how the above looks in the database.

1 JoÃ£o Î“Î¹ÏŽÏÎ³Î¿Ï‚
2 Anton TomÃ¡Å¡
3 KamilÄ— FranÄiÅ¡ka
4 æ„›å æ°è¥¿å¡

Any help appreciated.

Thank you

jpadie · Nov 9, 2012

when you say 'how it looks in the database' what do you mean? what tool are you using to view the fields in the database? have you checked that the tool itself supports utf8?

for example if you run this code does the screen look right

Code:

<?php 
header ('Content-Type: text/html; charset=UTF-8'); 
define('db_user','root');
define('db_password','test123');
define('db_host','localhost');
define('db_name','mydb');


// make the connection
@mysql_connect(db_host,db_user,db_password) or die('Could not connect to mysql');

@mysql_select_db(db_name) or die('could not select database');
mysql_set_charset('utf-8');
$result = mysql_query("select * from people");
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>Unicode in PHP</title>
</head>
<body style="font-size: 18pt;">
<em>Names from Around the World</em>
<table>
<?php  while ($row = mysql_fetch_assoc($result)): ?>
<tr><td><?php echo implode('</td><td>',$row); ?></td></tr>
<?php endforeach;?>
</table>

I would hope that using this code the names display correctly.

kzn · Nov 9, 2012

Hi thank you Jpadie and DonQuichote your help was spot on. Jpadie, I kept looking at the database through mysql and I could see it had strange characters but after running you script they have come up they way they should. Thanks again.

jpadie · Nov 10, 2012

for the command line client do this

Code:

mysql -u [username] -p
>>enter password

: USE {database name];
: SET NAMES utf8;
: SELECT * FROM people;

that should cause the command line client to behave itself and output meaningful utf8

you can also do this in php and ignore the mysql_set_charset. the setting should be sticky for the whole mysql session (usually the same as a page refresh for web apps).

Code:

mysql_query("SET NAMES utf8");

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How do I allow foreign characters through php to the database 2

kzn

MIS

jpadie

Technical User

DonQuichote

Programmer

kzn

MIS

kzn

MIS

jpadie

Technical User

kzn

MIS

jpadie

Technical User

Similar threads

Part and Inventory Search

Sponsor