0

I have all my database/tables and columns set to UTF-8_general_ci collation set.

Conditions that I Faced :-

  1. When I insert hindi data manually by phpmyadmin, I can see the the hindi characters in phpmyadmin, while question marks when seen on webpage generated by PHP
  2. In the same table when I insert data by HTML/PHP Forms I see some unrecognizable words in english something like cc2faa;(something like this) and Correct Hindi on Webpage.
  3. For the large data we have a script that reads from txt files and insert the data in the table in this , I see characters like जाना in phpmyadmin but Hindi On webpage.

Now the main problem is :-

Data has gone under changes online by forms and now I need this data to export to excel and give to the client but I am getting जाठin excel instead of Hindi Characters.

Note :-

  • All English characters are working fine and as it is everywhere.
  • My CHARACTER SET is utf8 For all tables.
  • I tried to change the collation to UTF-8_bin but that too doesn't helped me in anyway.
  • Encoding on the browser is UTF-8, and I have already sent the headers for UTF-8 encoding.

I have seen many posts about utf8 problem but no one seem to have this weird different behavior problem.

Please Do I have any rescue from this?? Or finally have to give the PHP reports of the data??

Please help!!

Sanuj
  • 1,077
  • 1
  • 11
  • 23
  • 1
    see: http://www.joelonsoftware.com/articles/Unicode.html – Omesh Sep 27 '12 at 08:00
  • Isn't there is any way when I connect the excel sheet to the database I get Hindi characters instead of those unreadable character?? – Sanuj Sep 27 '12 at 08:13
  • Try to run the following query before your select statement, "SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'" – Bud Damyanov Sep 27 '12 at 08:32

2 Answers2

6
  1. When I insert hindi data manually by phpmyadmin, I can see the the hindi characters in phpmyadmin, while question marks when seen on webpage generated by PHP

PHP probably generates the question marks because the encoding of the database connection is not utf-8. How to fix this depends on the database library you use; if you use MySQLi use mysqli_set_charset('utf8'), if PDO you add charset=utf8 to the DSN...

  1. In the same table when I insert data by HTML/PHP Forms I see some unrecognizable words in english something like cc2faa;(something like this) and Correct Hindi on Webpage.
  2. For the large data we have a script that reads from txt files and insert the data in the table in this , I see characters like जाना in phpmyadmin but Hindi On webpage.

These are likely caused by the same problem as above: the PHP forms and the script connect to the database using the default encoding, probably latin1. Then they insert utf-8 encoded text, but since MySQL thinks you are using latin1, it encodes the text into utf-8 again, and inserts this doubly encoded text into the table.

So: PHP sends "जाना" to MySQL telling it's latin1, and MySQL goes and converts it to utf-8, resulting in "जाना". Later PHP asks MySQL return the value, and since the connection is again using latin1, MySQL takes "जाना" and decodes it to latin1. Then PHP pretends that this latin1 string is actually utf-8 and displays "जाना".

Again, the solution is setting the encoding of the connection to utf-8. And this depends on what you use to access the database.

Joni
  • 108,737
  • 14
  • 143
  • 193
  • Thumbs Up, Why I haven't thought of that.. Now I understand, thanks a lot :) , you are right I corrected it now I just need to export and reimport the data once and adds up the charset to the connection string :) – Sanuj Sep 28 '12 at 04:34
1

If you need to export your data as Excel file, use the PHP class php-export-data by Eli Dickinson, http://github.com/elidickinson/php-export-data. It is pretty nifty and so far I had have no problems exporting weird character sets with it.

Bud Damyanov
  • 30,171
  • 6
  • 44
  • 52
  • Yes It helps me, Thank you. But For this I have to write different codes for different tables. Also Using this will not help me in connecting the excel to Database server. – Sanuj Sep 27 '12 at 08:10