0

Im trying to display some Indian language characters (Hindi, Tamil, etc) in a php file, taking the data from a MYSQL database.

It works fine with mysqli_query, but with PDO, the characters show up as garbage values. I am not able to figure out why, as I have set every possible aspect to work with utf-8.

Here is my connection string : (I am using PHP version 5.6.8)

$db_connection = new PDO('mysql:host=localhost;dbname=dbname;charset=utf8;', 'username', 'password', array(PDO::ATTR_EMULATE_PREPARES => false, 
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));

After doing some research I discovered the utf8_decode function, and decided to try that. It works only partially, however.

These are the results (as labeled)

PDO result without utf8 decoding:
आज सदीओ के बाद खà¥à¤¦à¤•à¥‹ हम संभाले हà¥à¤... 

PDO result with utf8 decoding:
��?��? सद��?��? ��?��? बाद ��?ुद��?��? हम स��?भाल��? हुए... 

MYSQLI_QUERY:
आज सदीओ के बाद खुदको हम संभाले हुए...

Since only PDO is having this problem, I'm assuming my DB and headers are fine. Please let me know if any more code is needed (Queries are standard DB calls).

EDIT: I have done the following steps already, with regards to utf-8 settings :

  1. Setting DSN - charset=utf8;
  2. Adding it to PDO's options array - PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
  3. Setting it via PDO object's exec function :

$db_connection->exec("SET NAMES utf8"); $db_connection->exec("SET CHARACTER SET utf8");

Sainath Krishnan
  • 2,089
  • 7
  • 28
  • 43
  • Make sure every part of your chain is expecting utf-8. Here's is a guide: http://www.toptal.com/php/a-utf-8-primer-for-php-and-mysql – Halcyon May 20 '15 at 13:01
  • Thanks for the speedy response! This is the guide that I used to get to this point :) I have followed every step given there already :( – Sainath Krishnan May 20 '15 at 13:03
  • Did you do `SET NAMES utf-8` as well? – Halcyon May 20 '15 at 13:04
  • Yes I did, using the `exec` function of my PDO object right? I also added it into the options array of the connection string itself (In the code above) – Sainath Krishnan May 20 '15 at 13:06
  • http://stackoverflow.com/questions/14042474/pdo-connection-utf-8-declaration-with-set-names-character-set – Saty May 20 '15 at 13:12
  • Thanks saty, I saw that one too. Pretty much the same as the primer that @Halcyon shared :) I have already done all of these steps – Sainath Krishnan May 20 '15 at 13:15
  • I have added in an EDIT that explains what I've tried already, a bit clearer :) – Sainath Krishnan May 20 '15 at 13:25
  • Are you sure that the data in your database is actually correctly stored?! If so, how have you confirmed that? A *very typical* problem is that the data in the database is actually hosed, since it was inserted with incorrect encoding settings on the database connection before. – deceze May 20 '15 at 13:28
  • Look at the results that I posted above. It works perfectly with `mysql_query` and `mysqli_query`, which is why I figured my encoding in the DB and my php headers are fine – Sainath Krishnan May 20 '15 at 13:29
  • How did you set your encoding in mysql/mysqli before? – deceze May 20 '15 at 13:30
  • Please see the duplicate, this is most likely your problem. Investigate that. If it's not, add some new insights to your question to clarify. – deceze May 20 '15 at 13:38
  • Thank you @deceze, the solution you provided seems to work. PDO is now working fine, but `mysql_query` has stopped working. Not that it is of concern, but is there any particular reason why? – Sainath Krishnan May 20 '15 at 13:52
  • Because you're setting the wrong charset in your mysql function. You need to configure the connection charset for PDO, mysql and mysqli independently. – deceze May 20 '15 at 13:54
  • Also, I just realised that if I remove all `utf-8` settings and change it to `latin1`, it works without a hitch. Even though these are clearly utf-8 characters... – Sainath Krishnan May 20 '15 at 13:56
  • Read this: [Handling Unicode Front To Back In A Web App](http://kunststube.net/frontback/), it explains this in detail. – deceze May 20 '15 at 14:08
  • Ouch! Using `latin1` only hides the problem, it does not solve it. And comparisons will not work correctly -- LIKE, >, etc. – Rick James Jun 26 '15 at 16:52
  • Please provide `SELECT col, HEX(col) FROM ...` so we can see if the text was stored correctly. Also provide `SHOW CREATE TABLE`. – Rick James Jun 26 '15 at 16:53

0 Answers0