rporter45
asked on
UTF-8 problem in mySQL
I have a UTF-8 mySQL database that is for some reason is displaying special characters incorrectly in the backend and when I export the data no matter what format while when viewing the data in the front end web application is displays correctly. Ultimately I am looking to export the data in an Excel or CSV file correctly. Can anyone help?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
In looking at the properties of the database that I restored in dbForge, it does have a Charset of utf8 however as I said the tables are not displaying special characters properly.
ASKER
MySQL is UTF-8
Table is UTF-8
Field is VarChar(200)
Font is Tahoma
MySQL Workbench showing - École
dbForge Studio showing - École
Front end web application showing - École
How do I get this to export into an Excel or CSV format showing the same as it does on front end?
Table is UTF-8
Field is VarChar(200)
Font is Tahoma
MySQL Workbench showing - École
dbForge Studio showing - École
Front end web application showing - École
How do I get this to export into an Excel or CSV format showing the same as it does on front end?
I suggest you request this thread be moved to a more appropriate topic area such as:
https://www.experts-exchange.com/Database/MySQL/
https://www.experts-exchange.com/Database/MySQL/
Got a neglected question alert on this one. Please see this article. It should help you sort out the pieces with things like École vs École. These are clear evidence of a character-set collision.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11880-Unicode-PHP-and-Character-Collisions.html
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11880-Unicode-PHP-and-Character-Collisions.html
Hi,
Have you tried using PhpMyadmin which is usually installed in package like Wampserver, Easyphp?
If it's online check with your hosting company you should have access to PhpMyAdmin or similare tool.
Using PhPMyAdmin
Select the database, click the 'Export' tab. Select the table to export. Use CSV as the format.
Use Office or other tool to open the csv file in the import box select UTF-8, this should display the data correctly.
if there are no tool you can use a php script tools:
SQLBuddy
http://sqlbuddy.com/
Make sure you select the good chartset when exporting and when importing.
Have you tried using PhpMyadmin which is usually installed in package like Wampserver, Easyphp?
If it's online check with your hosting company you should have access to PhpMyAdmin or similare tool.
Using PhPMyAdmin
Select the database, click the 'Export' tab. Select the table to export. Use CSV as the format.
Use Office or other tool to open the csv file in the import box select UTF-8, this should display the data correctly.
if there are no tool you can use a php script tools:
SQLBuddy
http://sqlbuddy.com/
Make sure you select the good chartset when exporting and when importing.
I haven't subjected this to any testing, but the comment sounds like it may be on point.
http://php.net/manual/en/function.fputcsv.php#94088
I have tested this guidance from Princeton University and it worked perfectly. Seems Excel assumes "ANSI" characters. That assumption allows Excel to read the UTF-8 data, but the display of the data is garbled.
http://www.itg.ias.edu/content/how-import-csv-file-uses-utf-8-character-encoding-0
When the imported CSV was saved as an Excel workbook (.xlsx) and Excel was restarted to read the file, the display was correct.
http://php.net/manual/en/function.fputcsv.php#94088
I have tested this guidance from Princeton University and it worked perfectly. Seems Excel assumes "ANSI" characters. That assumption allows Excel to read the UTF-8 data, but the display of the data is garbled.
http://www.itg.ias.edu/content/how-import-csv-file-uses-utf-8-character-encoding-0
When the imported CSV was saved as an Excel workbook (.xlsx) and Excel was restarted to read the file, the display was correct.
ASKER