Converting HTML foreign characters from MySQL tables to utf-8 characters

Question

I have had issues with my MySQL and using utf-8 and I ended up using HTML characters, but I am starting to regret that very much. I now have 4 tables with a lot of foreign characters saved as HTML.

I have been able to rewrite programming and setup MySQL to process utf-8 properly, but what would be the best way to convert the strings to utf-8?

INSERT INTO `languages` (`id`, `title`, `native`, `alias`, `status`, `weight`, `updated`, `created`) VALUES
(1, 'English', 'English', 'en', 1, 1, '2009-11-02 21:37:38', '2009-11-02 20:52:00'),
(2, 'Dutch', 'Nederlands', 'nl', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(8, 'French', 'Français', 'fr', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(3, 'Spanish', 'Español', 'es', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(4, 'Italian', 'Italiano', 'it', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(6, 'German', 'Deutsch', 'de', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(7, 'Portuguese', 'Português', 'pt', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(11, 'Swedish', 'Svenska', 'sv', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(9, 'Polish', 'Polski', 'pl', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(12, 'Russian', '&#1056;&#1091;&#1089;&#1089;&#1082;&#1080;&#1081;', 'ru', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(13, 'Afrikaans', 'Afrika', 'af', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(14, 'English', 'English', 'en', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(15, 'Albanian', 'Shqip', 'sq', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(16, 'Arabic', '&#1575;&#1604;&#1593;&#1585;&#1576;&#1610;&#1577;', 'ar', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(17, 'Farsi', '&#1575;&#1604;&#1601;&#1575;&#1585;&#1587;&#1610;&#1577;', 'fa', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(18, 'Chinese (traditional)', '&#20013;&#25991;(&#32321;&#39636;)', 'cht', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(19, 'Japanese', '&#26085;&#26412;', 'ja', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(20, 'Latin', 'Latina', 'la', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(21, 'Chinese (simplified)', '&#20013;&#25991;(&#31616;&#20307;)', 'chs', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(22, 'Turkish', 'Türkçe', 'tr', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(23, 'Catalan', 'Català', 'ca', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(24, 'Hindi', '&#2361;&#2367;&#2344;&#2381;&#2342;&#2368;', 'hi', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(25, 'Hungarian', 'Magyar', 'hu', 1, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00');

Above is an example of SQL data.

Marc B · Accepted Answer · 2011-08-09 21:37:42Z

2

Round trip the data through PHP. Do a select, grab the relevant fields and run them through htmlentities() to convert back into actual characters, then stuff the data back into the database.

MySQL itself doesn't have any entity encoding/decoding support, so doing the round trip is the quickest/easiest fix.

answered Aug 9, 2011 at 21:37

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

3 Comments

hakre Over a year ago

Or within MySQL, but maybe not that convenient for the OP: forums.mysql.com/read.php?98,246527

Dennis Over a year ago

I tried both htmlentities($input) and html_entity_decode($input), but both fail to decode to utf-8.

Marc B Over a year ago

php.net/htmlentities there's a 3rd parameter for the function to specify charsets.

Collectives™ on Stack Overflow

Converting HTML foreign characters from MySQL tables to utf-8 characters

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related