Convert latin1 characters on a UTF8 table into UTF8
You have configured your PHP environment correctly: mb_internal_encoding('UTF-8'), UTF-8 files, and an InnoDB table with utf8_unicode_ci collation. You also set the connection charset to UTF-8. Despite this, legacy data inserted without proper encoding appears garbled, often displaying as Jáuò Iñe. This is a classic double-encoding issue where bytes intended for Latin-1 were interpreted as UTF-8 on insert and are now stored in the database incorrectly.
Since your new inserts work perfectly, the challenge is migrating existing rows that contain this specific corruption pattern without breaking the string processing pipeline. The primary goal is to convert the stored mojibake back into valid UTF-8 characters.
Understanding the Data Corruption
The sequence à followed by an accented character (e.g., Já) indicates a UTF-8 byte sequence was interpreted as ISO-8859-1 (Latin-1) during insertion. Specifically, the two-byte UTF-8 representation of á (0xC3 0xA1) was read as two separate Latin-1 characters: à and ¡.
To fix this, you must reverse that process: decode the stored bytes as Latin-1 first, then re-encode them into UTF-8. This is a standard conversion pattern for cleaning up legacy databases.
The Correct Conversion Strategy
The most robust approach uses PHP's mb_convert_encoding or iconv. You need to treat the data currently in your database column as if it were encoded in ISO-8859-1, even though your connection is set to UTF-8.
Here is a safe pattern to perform an update in a single query block. Note that we are converting the string representation directly; we do not need to toggle the MySQL client charset mid-query for the conversion logic itself, provided we handle the string values correctly in PHP.
// Fetch rows one by one to process them safely
$result = mysql_query('SELECT * FROM `table`');
while ($row = mysql_fetch_assoc($result)) {
$badName = $row['name'];
// Convert from the stored Latin-1 (ISO-8859-1) interpretation to UTF-8
$fixedName = mb_convert_encoding($badName, 'UTF-8', 'ISO-8859-1');
if ($fixedName !== $badName) {
// Escape the fixed string safely for SQL injection prevention
$escapedName = mysql_real_escape_string($fixedName);
// Update only if a change actually occurred
mysql_query('UPDATE `table` SET `name` = "' . $escapedName . '" WHERE a1 = "' . mysql_real_escape_string($row['a1']) . '"');
}
}
Why Your Previous Attempts Failed
The code snippet in your source material attempted to toggle the MySQL charset between UTF-8 and Latin-1 within the same session. While toggling mysql_set_charset() changes how MySQL handles incoming queries, it does not magically change the encoding of a string variable passed in PHP.
When you ran:
$escaped = mysql_real_escape_string('¿<...>...');
mysql_query('INSERT ... SET name="' . $escaped . '"');
The mysql_real_escape_string function works on bytes. If your internal encoding is UTF-8, the special characters in your test string might have been double-encoded or mangled before escaping, depending on how that specific string was constructed in memory.
Furthermore, the truncation you observed after the character Ä suggests a mismatch between the expected multi-byte sequence and the actual byte stream being processed. When using iconv, adding flags like //IGNORE or //TRANSLIT can sometimes mask the issue but often result in data loss or unexpected substitutions if the source encoding detection is ambiguous. The explicit conversion from ISO-8859-1 to UTF-8 is more predictable for this specific "double-encoded" scenario.
Handling Edge Cases and Limitations
This fix assumes a strict pattern: every byte sequence that looks like a two-byte UTF-8 character in the database was actually intended as two single-byte Latin-1 characters. If your legacy data contains valid Latin-1 characters that are not double-encoded UTF-8 (such as a genuine ñ stored as 0xE9), converting them from ISO-8859-1 to UTF-8 will result in the same byte sequence, which is harmless.
However, if your database contains a mix of valid Latin-1 text and double-encoded UTF-8 text, you must ensure that your conversion logic does not alter the valid parts. The mb_convert_encoding function generally handles this well because converting a pure ISO-8859-1 byte to its UTF-8 equivalent is an identity operation for single-byte characters, while fixing the corruption for multi-byte sequences.
Always test the conversion on a backup or a staging environment first. Run a selection query with mb_detect_encoding (or manual hex inspection) to verify the pattern before running bulk updates. If you encounter characters that cannot be converted cleanly, consider using iconv($string, 'UTF-8//IGNORE', 'ISO-8859-1') only as a last resort to prevent script crashes, but be aware this will drop problematic data.
For modern Laravel projects, if you are migrating from these legacy scripts, it is often cleaner to run a one-time migration job that executes the conversion logic described above, rather than trying to handle encoding mismatches in every controller or model layer.