2

I have develop a web site using MySQL 8.0 (uft8mb4) and PHP 5.6 (UTF-8) Where no issues where raised for the INSERT or SELECT data in the DB. ex. the word "sévère" would appear as is in both parties.

The problem appeared when I transfered the web site online. Now using MySQL 5.6 (uft8mb4) and PHP 5.6 (UTF-8) However in PHP the characters (é,è, ê, exc..) appears not as wanted.

In DB : sévère

In PHP : s�v�re

If I INSERT the data from PHP to DB

In DB : s�v�re

In PHP : sévère

I get the opposite.

I assume that it is not caused by uft8mb4 vs utf8 since I didn't have the issue before, even though I was using the same configuration. However, it seems the it is related.

How can I resolve this, so that both PHP display ( SELECT) and the DB (INSERT) display the same exact information ?

David
  • 39
  • 1
  • 1
  • 7
  • `utf8mb4` and `utf8` are both UTF-8 and for characters that can be represented as 1-3 bytes will be identical. The `mb4` part accommodates things like emoji, 4-byte characters. If you're seeing errors like this it's because the data isn't actually UTF-8. Check with `SELECT HEX(column_name)` to see what's actually stored in it. Letters like é are normally only two bytes. – tadman Jul 06 '18 at 17:58
  • "sévère" should contain the bytes `73c3a976c3a87265` but if you're getting `73e976e87265` then that's being sent in Windows-1252 encoding. – tadman Jul 06 '18 at 17:59
  • When I INSERT "sévère" from PHP into the DB, In the DB I get the following... Name : sévère HEX : 73C383C2A976C383C2A87265 What should I understand ? – David Jul 06 '18 at 18:11
  • 1
    All it takes, is one wrong charset setting in your application - *everything* needs to be the same charset! I have previously written [**an answer about UTF-8 encoding**](https://stackoverflow.com/a/31899827/4535200) that contains a little checklist, that will cover *most* of the charset issues in a PHP/MySQL application. There's also a more in-depth topic, [**UTF-8 All the Way Through**](https://stackoverflow.com/q/279170/4535200). Most likely, you'll find a solution in either one or both of these topics. – Qirel Jul 06 '18 at 18:13
  • That's the result of double UTF-8 encoding. It looks like UTF-8 data was mistakenly interpreted as ISO-8859-1 and then re-encoded as UTF-8. The data is bad and needs to be fixed. – tadman Jul 06 '18 at 18:53
  • Read this: https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James Jul 06 '18 at 20:36
  • Those accented letters are the same in both utf8 and utf8mb4. – Rick James Jul 06 '18 at 20:37
  • I modified all my DB with using 'utfmb4' Every Tables with columns having VARCHAR are set with **CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'** After that the issue was still not resolved. In my PHP, before every query I added the code.... **mysqli_query($this->mysqli, "SET NAMES 'utf8'");** Now it works perfectly. Thank you all – David Jul 10 '18 at 20:28

0 Answers0