strange character encoding of stored data , old script is showing them fine new one doesn't
In short, because this has been discussed a thousand times before:
- PHP holds a string, say
"漢字"
, encoded in UTF-8. The bytes for this areE6 BC A2 E5 AD 97
. - It sends this string over a database connection which is set to
latin1
. - The database receives the bytes
E6 BC A2 E5 AD 97
, thinking those representlatin1
characters. - The database stores the characters
æ¼¢å
(the characters thatE6 BC A2 E5 AD 97
maps to inlatin1
). - The same process reversed makes PHP receive the same bytes, which it then treats as UTF-8. The roundtrip works fine for PHP, even though the database doesn't treat the characters as it should.
So the problem here was that the database connection was set incorrectly when the data was entered into the database. You'll have to convert the data in the database to the correct characters. Try this:
SELECT CONVERT(BINARY CONVERT(field_name USING latin1) USING utf8) FROM table_name
Maybe utf8
isn't what you need here, experiment. If that works, change this into an UPDATE
statement to update the data permanently.
MySQL data has wrong encoding when displaying using PHP
OK. Comments by sbondo1234 and Tausif helped me to realize that the new records work just fine. Only the old data in the database is messed up. It was easier for me to create a simple matrix and pair incorrectly encoded characters with correct ones and then update them with simple SQL queries. Thanks for your help.
Encoding error with polish charset during transfer of database / server seting up
At the end I have founded out that the problem was related to the fact that the data was written to SQL incorrectly in my original server.
I ended up with transferring DB using:
mysqldump --default-character-set=utf8 [ORYGINAL_DB] | mysql [TARGET_DB] --default-character-set=utf8
and the executing:
UPDATE [table name] SET [field] = CONVERT(BINARY CONVERT([field] USING latin2) USING utf8)
as it was advices here:
strange character encoding of stored data , old script is showing them fine new one doesn't
Hope that the above solution will be helpful for others too.
Related Topics
How to Send 100,000 Emails Weekly
How to Replace "If" Statement With a Ternary Operator ( : )
How to Explain Composer'S Error Log
How to Get a Substring Between Two Strings in PHP
Generating Random Results by Weight in PHP
Giving My Function Access to Outside Variable
Insert New Item in Array on Any Position in PHP
PHP Elvis Operator VS Null Coalescing Operator
Recursive Function to Generate Multidimensional Array from Database Result
Best Way to Get Result Count Before Limit Was Applied
Detecting Request Type in PHP (Get, Post, Put or Delete)
Failed to Connect to Mailserver At "Localhost" Port 25
Send JavaScript Variable to PHP Variable
Angularjs Http Post to PHP and Undefined