I am creating a toy website locally with XAMPP and I want it to support Unicode.
index.php contains a simple form that I can use to add a new entry (a UTF-8 string) to a table "myTable" in the DB. In addition to that, I am also echoing with PHP every entry from "myTable" on the body of index.php, so that I can see if the UTF-8 strings that I am entering through the form make all the way into the database and back without any issues.
Here are the measures that I took toward Unicode support:
- Every file on my website (ex.: index.php) is encoded in UTF-8 without BOM;
<meta charset='utf-8'>everywhere applicable too;
mysqli_set_charset($myConnection, "utf-8");in my "db.inc.php" file;
- At the phpMyAdmin "control panel" (?), under "Database Server" I see "Server charset: UTF-8 Unicode (utf8)";
I created a database and a table for this specific purpose. Here is the result of
SHOW CREATE TABLE `myTable`:
CREATE TABLE `myTable` ( `row1` varchar(16) COLLATE utf8mb4_bin NOT NULL, `row2` varchar(32) COLLATE utf8mb4_bin NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
The outcome was as follows: when I print the entries of "myTable", everything does get displayed correctly on the browser indeed, however only owing to a stroke of luck, because apparently my strings are being internally represented badly. When I browse my table with phpMyAdmin I see mojibake, and I looked over at
myTable.ibd and I see the same mojibake.
joão e maría for example, is displayed as
joÃ£o e marÃa; for the first
ã -> Ã£, it could be UTF-8 bytes being interpreted as latin-1, though I see
Ã£ even when I tell npp to interpret the file as UTF-8.
Running the query
SELECT `row1`, HEX(`row1`) FROM `myTable` WHERE 1 I see
6A6FC383C2A36F2065206D6172C383C2AD61 as the bytes of
joÃ£o e marÃa, so in fact the mojibake is "correctly" encoded as UTF-8 (
C3 83 C2 A3 is UTF-8 for
After more careful inspection I verified that
mysqli_set_charset($myConnection, "utf-8"); was returning
false. I replaced it with
mysqli_set_charset($myConnection, "utf8"); and voilà, strings with fancy characters that I enter through the form now make it all the way into the database unscathed. However, these new strings aren't correctly displayed by the browser.
For example: the string
bênção is inserted into the DB through the form. I query the table with
SELECT `row`, HEX(`row1`) (...) and find out that it is stored as
62C3AA6EC3A7C3A36F inside the table, which is the correct UTF-8 representation of the string in question. I access index.php and I see
b�n��o being displayed. I download the HTML of the webpage and inspect it, and I find that the offending bytes are respectively
E3, which, surely enough, correspond to
ã in latin-1.