MySQL - convert latin1 characters to UTF8 table in UTF8

Question

Only today I realized that in my PHP scripts I was missing:

mysql_set_charset('utf8'); 

All my tables are InnoDB, sorting is "utf8_unicode_ci", and all my VARCHAR columns are also "utf8_unicode_ci". I have mb_internal_encoding('UTF-8'); on my PHP scripts, and all my PHP files are encoded as UTF-8.

So, so far, every time I "INSERT" something with diacritics, for example:

 mysql_query('INSERT INTO `table` SET `name`="Jáuò Iñe"'); 

The contents of 'name' will then be: Jáuò Iñe : Jáuò Iñe .

Since I fixed the encoding between PHP and MySQL, the new INSERTs are now stored correctly. However, I want to fix all the old lines that are currently "messed up." I already tried a lot, but it always breaks the lines into the first "illegal" character. Here is my current code:

 $m = mysql_real_escape_string('¿<?php echo "¬<b>\'PHP &aacute; (á)ţăriîş </b>"; ?> ă-ţi abcdd;//;ñç´พดแทฝใจคçăâξβψδπλξξςαยนñ ;'); mysql_set_charset('utf8'); mysql_query('INSERT INTO `table` SET `name`="'.$m.'"'); mysql_set_charset('latin1'); mysql_query('INSERT INTO `table` SET `name`="'.$m.'"'); mysql_set_charset('utf8'); $result = mysql_iquery('SELECT * FROM `table`'); while ($row = mysql_fetch_assoc($result)) { $message = $row['name']; $message = mb_convert_encoding($message, 'ISO-8859-15', 'UTF-8'); //$message = iconv("UTF-8", "ISO-8859-1//IGNORE", $message); mysql_iquery('UPDATE `table` SET `name`="'.mysql_real_escape_string($message).'" WHERE `a1`="'.$row['a1'].'"'); } 

"UPDATE" with the expected characters, except that the string is truncated after the character "ă". I mean that this character and the following characters are not included in the string.

Also, testing with "iconv ()" (which is commented out by the code) does the same, even with // IGNORE and // TRANSLIT

I also checked several encodings between ISO-8859-1 and ISO-8859-15.

I really need help here! Thank.

Answer 1

From what you described, it seems that you have UTF-8 data that was originally saved as Latin-1 and then converted incorrectly to UTF-8. Data can be recovered; you will need a MySQL function like

 convert(cast(convert(name using latin1) as binary) using utf8) 

You may need to omit the internal conversion, depending on how the data was changed during the encoding conversion.

Answer 2

After I checked about an hour or two for this answer. I needed to transfer the old tt_news db from a typo to the new typo3 version. I already tried converting the encoding to an export file and importing it back, but did not get its job.

Then I tried to answer above with ABS and started updating the table:

 UPDATE tt_news SET title=convert(cast(convert(title using latin1) as binary) using utf8), short=convert(cast(convert(short using latin1) as binary) using utf8), bodytext=convert(cast(convert(bodytext using latin1) as binary) using utf8) WHERE 1 

You can also convert imagecaption, imagealttext, imagateitletext and keywords if necessary. Hope this helps someone upgrade to tt_news on the new version of typo3.

Answer 3

the way is better to use the connection database tow normal

then use this code to do what you need you have to make your page encoded with utf-8 meta in the html cod header (don't forget about it)

then use this code

  $result = mysql_query('SELECT * FROM shops'); while ($row = mysql_fetch_assoc($ $name= iconv("windows-1256", "UTF-8", $row['name']); mysql_query("SET NAMES 'utf8'"); mysql_query("update `shops` SET `name`='".$name."' where ID='$row[ID]' "); }