strange character encoding of stored data , old script is showing them fine new one doesn’t

by

In short, because this has been discussed a thousand times before:

  1. PHP holds a string, say "漢字", encoded in UTF-8. The bytes for this are E6 BC A2 E5 AD 97.
  2. It sends this string over a database connection which is set to latin1.
  3. The database receives the bytes E6 BC A2 E5 AD 97, thinking those represent latin1 characters.
  4. The database stores the characters æ¼¢å­ (the characters that E6 BC A2 E5 AD 97 maps to in latin1).
  5. 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.

More Related Contents:

  1. PHP – Change value y,n in database [closed]
  2. how to select only one value sql
  3. Gather column name of a table from database [closed]
  4. I am seeing this error on submitting form Error: query was empty [closed]
  5. How to convert the json image url string into Bitmap image to display images in android application
  6. CodeIgniter – foreach loop
  7. save user images in MYSQL database by which method? [closed]
  8. How to use multiple databases in Laravel
  9. How to insert multiple rows from array using CodeIgniter framework?
  10. Warning: mysqli_connect(): (HY000/1045): Access denied for user ‘username’@’localhost’ (using password: YES)
  11. CodeIgniter: Unable to connect to your database server using the provided settings Error Message
  12. Importing CSV data using PHP/MySQL
  13. Convert latin1 characters on a UTF8 table into UTF8
  14. How to store file name in database, with other info while uploading image to server using PHP?
  15. Find total number of results in mySQL query with offset+limit
  16. PDO::PARAM for type decimal?
  17. How to store an array into mysql?
  18. PHP + SQL Server – How to set charset for connection?
  19. MySQL diacritic insensitive search (spanish accents)
  20. Get all child, grandchild etc nodes under parent using php with mysql query results
  21. What are the differences between backtick and single quote? Can I use IF statement in a query as above?
  22. How to execute raw queries with Laravel 5.1?
  23. Trying to get property of non-object – CodeIgniter
  24. Saving images in database mysql
  25. PHP – Secure member-only pages with a login system
  26. Advantages / Disadvantages of pconnect option in CodeIgniter
  27. UTF-8 character encoding battles json_encode() [duplicate]
  28. Save Accents in MySQL Database
  29. How do I save PHP session data to a database instead of in the file system?
  30. Escaping SQL queries in Codeigniter

Leave a Comment