0

I recently changed hosting companies and migrated whm and cpanel to new host. Moved into new server using the WHM cpanel transfer tool.

Immediately noticed some character encoding issues, and manually fixed a bunch of then in mysql. These issues were not really recognizable in mysql phpmyadmin but were noticable on webpages as black diamonds with a ? inside them. We continue to see encoding issues, some of which is people cutting and pasting from word, (no matter how much I ask them not to do that). I have also noticed that user input from ipads via an in-house app I developed which posts data to the server will cause different character encoding issues. All my mysql tables are set to utf8_unicode_ci

Example, On ipad I type in

Desc _. -  “. ‘ now

and in mmsql I see this

Desc _. -  â€œ. ‘ now

On webpage it looks ok but it causes display issues in certain places like PDF generator.

Is it a Mac thing? An Xcode thing? How can I correct this before adding or updating record with php / mysql?

Mike Volmar
  • 1,927
  • 1
  • 22
  • 31
  • 1
    You should be using at least `utf8mb4_unicode_ci` as `utf8_unicode_ci` does not support multi-byte characters, which is why you are seeing the multiple characters that together represent a singular character. Multi-byte characters essential require 4 characters to create a single character, in your example you're using magic quotes which are not standard quotes, they are multi-byte. Your website is able to interpret this just fine, but you should convert your database utf8mb4 as soon as possible. – zanderwar Jan 20 '23 at 14:38
  • 1
    These problems happen when you rely on default encodings. If server settings change, you're screwed. Review [this question](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) and double-check you're setting UTF-8 explicitly at every level mentioned. – Álvaro González Jan 20 '23 at 15:01
  • 1
    See "black diamond" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored . Also, explain how you moved the data from the old server -- do you still have the dump? We may need to look at it so see if the data was corrupted _before_ loading on the new server. – Rick James Jan 21 '23 at 00:52
  • I altered my db and some tables to utfmb4 as other post describes, but adding records from ipad are still producing these funky characters – Mike Volmar Jan 23 '23 at 16:04
  • I added this to my php file and problem is solved. $mysqli->set_charset("utf8mb4"); – Mike Volmar Jan 23 '23 at 16:38
  • Voting to reopen because "converting the entire db" is not the answer. – Rick James Jan 26 '23 at 04:16

1 Answers1

0

It is quite OK to INSERT utf8 characters, but you must tell the connection that the client is using UTF-8. That is the main reason for getting Mojibake like “ and ‘.

Yes it would probably be good to change the entire database, not just to utf8, but utf8mb4. How to convert an entire MySQL database characterset and collation to UTF-8? discusses such.

This discusses the causes for M9jibake: Trouble with UTF-8 characters; what I see is not what I stored

Rick James
  • 135,179
  • 13
  • 127
  • 222