UTF-8 special characters are inserting into table as weird characters

Question

I searched in this site there were lots of answers but none of them are working for me. I am trying to parse the below XML using PHP simplexml_load_string.

My XML is:

<?xml version="1.0" encoding="utf-8"?>
<address>
<name>Peter</name>
<country>Großbritannien</country>
</address>

When I print it using print_r the result is showing as below:

SimpleXMLElement Object
(
    [name] => Peter
    [country] => GroÃŸbritannien
 )

and when I use ini_set('default_charset', 'UTF-8') or header('Content-Type: text/html; charset=utf-8') the result is:

SimpleXMLElement Object
(
    [name] => Peter
    [country] => Großbritannien
)

But When I try to insert the country value using PHP (with the header set as utf8) in the database (MySQL) then its inserting as GroÃŸbritannien. My tables character-set is UTF8 and collation of the column is utf8_unicode_ci. However when I directly insert the country value into the table(using phpMyAdmin or terminal) then its inserting properly.

I would like to know why the country value is not inserting properly from my PHP parsing page.

My PHP sample code is below:

$notificationXml  = simplexml_load_string($xml); 
$con = $notificationXml->country;
mysql_query("INSERT INTO test_1 (con) values ('$con')");

Please help me out. Thanking you all in advance.

Have you used the correct charset for your _database connection_? — CBroe, Jun 27 '13 at 11:06
I have added my code to the question. The country value is inserting properly when i am using insert from terminal or phpMyAdmin which shows that the characterset is set properly. — Suraj, Jun 27 '13 at 11:17
You need to change the connection charset to UTF-8 with [`mysql_set_charset`](http://php.net/manual/en/function.mysql-set-charset.php). However, the `mysql` extension is deprecated and generally considered not secure enough, so please switch to something better like `mysqli` or `PDO`. — Jon, Jun 27 '13 at 11:18
add `mysql_query( "SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'" );` after creating the connection and before inserting datas — bystwn22, Jun 27 '13 at 11:24
I changed from mysql to mysqli and also used mysqli_query($con, 'SET character_set_results=utf8'); But not working. — Suraj, Jun 27 '13 at 11:26
[Handling Unicode Front To Back In A Web App](http://kunststube.net/frontback/), [What Every Programmer Absolutely, Positively Needs To Know About Encodings And Character Sets To Work With Text](http://kunststube.net/encoding/) — deceze, Jun 27 '13 at 11:26

code_rum · Answer 1 · 2013-06-27T11:47:33.260

1

Insert this above your insert query

mysql_query('SET NAMES utf8');

AS @deceze pointed out It is better to use

mysql_set_charset('utf8');

edited Jun 27 '13 at 11:47

answered Jun 27 '13 at 11:21

code_rum

872
6
21

3

Yes, but no. Please see http://stackoverflow.com/a/12720360/476. – deceze Jun 27 '13 at 11:27
1

@Suraj: **Don't do this**. Use `mysql_set_charset` instead. – Jon Jun 27 '13 at 11:32

VolkerK · Accepted Answer · 2013-06-27T11:36:17.683

a) the extension providing the mysql_* functions is marked as deprecated. Better to start with pdo_mysql or mysqli
b) You have to take care of the connection charset, i.e. essentially the charset the MySQL server expects the client to use when sending/receiving data. Avoid using something like SET NAMES ... as it would leave the client side mysql library in the dark about the new charset/encoding rules, so some character handling may not work as intended which may even lead to security related issues. Use the dedicated client side mechanism for changing the charset instead. For mysql_* that would be mysql_set_charset()., for mysqli_* mysqli::set_charset() and for pdo you should put that information into the dsn like

$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8')

(and use a php version >= 5.3.6)

UTF-8 special characters are inserting into table as weird characters

2 Answers2