Editing UTF-8 data

User, date Message
Written by Dragory
1 year ago
Category: Feature discussion
4 posts since Wed, 01 Feb 12 		Hey!

I tried to edit a database I have in utf8_unicode_ci collation (and so are all of the tables and their columns), but the unicode characters I insert are not encoded in UTF-8 when sent to the server, and thus display incorrectly on a website later on. Is there a way to change the character set of HeidiSQL itself?

Unicode characters inserted as UTF-8 from other sources also display with some weird symbols when viewing with HeidiSQL.

Thanks in advance!
Written by kalvaro
1 year ago
442 posts since Thu, 29 Nov 07 		HeidiSQL works fine with UTF-8. I use it all the time.

Are you sure your existing data is correctly stored? There's a simple test you can do: save the euro symbol (€) from your other source and run a query with the HEX() function, e.g.:

SELECT HEX(column_name) FROM table_name WHERE record_id=123



If you return this:

E282AC
Written by kalvaro
1 year ago
442 posts since Thu, 29 Nov 07 		Typo: it should return
Written by Dragory
1 year ago
4 posts since Wed, 01 Feb 12 		Thanks for the reply!

The one I inserted myself from HeidiSQL returned E282AC, but the one inserted via a query in a PHP file returned C3A2E2809AC2AC. The latter one works and displays correctly in browsers (site's charset is set to UTF8), but incorrectly in HeidiSQL (shown as "€" there).

Any idea what I might be doing wrong?
Written by Dragory
1 year ago
4 posts since Wed, 01 Feb 12 		Typo: UTF-8 (not with the smiley)
Written by kalvaro
1 year ago
442 posts since Thu, 29 Nov 07 		I'm afraid that your data is already corrupted. I've seen that situation several times: some PHP code is missing the "UTF-8" bit somewhere, typically in the connection settings. E.g. for PDO:

$conn = new PDO('mysql:host=localhost;dbname=database', 'user', 'pass', array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
//PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', <-- Missing
));



Your browser receives €from the user and sends € to MySQL, but the same process is done when retrieving data so nobody notices because data looks good from within the app.
Written by Dragory
1 year ago
4 posts since Wed, 01 Feb 12 		Ah, I wasn't aware that "SET NAMES utf8" was needed. Thank you, the data is now consistent!smile
 

Please login to leave a reply, or register at first.