How to fix garbled characters in PHPMyAdmin-CodePudding

My MySQL database contains some Chinese symbols and such (non-ASCII symbols). When I view them in PHPMyAdmin, they look garbled. However, if I display them on my website with PHP using the regular

If I export the database data in MyPHPAdmin, the export is also garbled in Notepad , I made sure to view it with UTF-8 encoding. If I import the garbled export again, the database will show the data as garbled once more and on the website the data now also shows as garbled. In this case, an actually corrupted export happened.

How can I solve this encoding problem? Clearly PHP can handle UTF-8 properly, my Apache web server is also serving UTF-8 and my database is configured seemingly correctly as well but there is an issue with PHPMyAdmin or the database/database table collation.

CodePudding user response：

It looks like the issue was entirely elsewhere since I'm supplying data to PHP with C code. The C code uses the nlohmann JSON libary to build the data submitted to the PHP script. The issue was my inability to specifically encode std::strings to UTF-8 like described here when putting data into a C JSON object. With that said, everything is now working as expected.

CodePudding user response：

⚈  If using mysqli, do $mysqli_obj->set_charset('utf8mb4');
⚈  If using PDO do somethin like $db = new PDO('dblib:host=host;dbname=db;charset=utf8mb4', $user, $pwd);
⚈  Alternatively, execute SET NAMES utf8mb4

Any of these will say that the bytes in the client are UTF-8 encoded. Conversion, if necessary, will occur between the client and the database if the column definition is something other than utf8mb4.

More notes on PHP: http://mysql.rjweb.org/doc.php/charcoll#php

If you have specific garbling, see Trouble with UTF-8 characters; what I see is not what I stored

If you suspect the data being fed from PHP to Notepad, dump a few Chinese characters in hex and shown to us. I would expect every 4th character to be hex F0 or every 3rd to be between E3 and EA. (These are the first byte for 4-char and 3-char UTF-8 encoding of Chinese characters.)

Does Notepad properly handle UTF-8, or does it need a setting?

If you are in the "cmd" in Windows, you may need chcp 65001; see http://mysql.rjweb.org/doc.php/charcoll#entering_accents_in_cmd That way, more non-English characters will display correctly.