mysql + php encoding

Question

I know there were plenty of questions like this but I am creating the new one because to my point of view it is specific to each situation.

So, my page is displayed in UTF-8 format. The data is taken from mySQL that has utf8_unicode_ci collation. The data I am displaying is the string - 1 Bröllops-Festkläder.

There are some unicode characters in here and they should display fine but they do not. On my page these are just a bunch of hieroglyphs.

Now, the interesting situation:

I am using phpMyAdmin to keep track of what is happening in the database. The website has the ability to import CSV documents containing customer data and modify each customer individually. If I import CSV document containing these characters they are written to the database, readable in phpMyAdmin and not readable on my page. If I use my script to modify the customer information and I type those characters from the browser, the it is vice versa - they are readable on the page and they are not readable in phpMyAdmin, so clearly the encoding is different. I spent ages figuring out the right combination and I could not.

UPDATE: Deceze posted a link below that I copy here to make it more noticeable. I am sure this will save hours and days to many people facing similar issues - Handling Unicode Front to Back in a Web App

All explained in-depth here: Handling Unicode Front To Back In A Web App — deceze
– deceze ♦, Commented Aug 27, 2012 at 14:40
@deceze, while ago that helped me understand lots of things. Thank you for writing it. — Adi
– Adi, Commented Aug 27, 2012 at 14:42
Stefan, I am afraid it is not possible as this is a backend of a functional CRM. Deceze, thank for sharing the link. I will read it through — Vladimir Hraban
– Vladimir Hraban, Commented Aug 27, 2012 at 14:44
You should probably set the connection charset with mysql_set_charset. @deceze great article — Giuseppe
– Giuseppe, Commented Aug 27, 2012 at 14:46

Marcin Orlowski · Accepted Answer · 2012-08-27 15:04:43Z

3

There're couple of things that got involved here. If your database encoding is fine and html encoding is fine and you still see artefact, it's most likely your db connection is not using same encoding, thus leading to data corruption. If you connect by hand, you can easily enforce utf encoding, by doing query SET NAMES UTF8 as very first thing after you connect() to your database. It is sufficient to do this only once per connection.

EDIT: one important note though - depending on how you put your data to the DB, your database content may require fixing as it can be corrupted if you put it via broken connection. So, if anyone is facing the same issue - once you set all things up, ensure you are checking on fresh data set, or you may still see things incorrectly, even all is now fine.

answered Aug 27, 2012 at 15:04

Marcin Orlowski

75.7k11 gold badges128 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

deceze Over a year ago

Don't query "SET NAMES", use mysql_set_charset.

Vladimir Hraban Over a year ago

In PDO variation this is $db = new PDO("mysql:host=HOST;dbname=DBNAME", "LOGIN", "PASS", array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8') );

Marcin Orlowski Over a year ago

Don't use mysql_* extension. Use mysqli_* :) Anyway, my answer is - beside DB schema and website encoding, there's db link that can also wreck your data. It's not obvious for many people. The way you address this is irrelevant, and depends on used libraries/drivers. If you'd be using Zend Framework for example, you should add resources.db.params.charset = UTF8 to your application.ini file instead

Collectives™ on Stack Overflow

mysql + php encoding

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related