Chazy Chaz Chazy Chaz - 1 year ago 88
SQL Question

php not treating characters from mysql as utf-8

Sorry for the title, I've been searching/reading about what/where can the problem be and I'm already too confused about this...

I recently started using utf8mb4 in mysql. I'm using utf8mb4 as charset and utf8mb4_unicode_ci as collation for all tables/columns.

So I first I changed:




made sure my php files are utf8 (I'm using Visual Studio Code so the files are created in UTF-8 by default), and php/html headers are set to utf8:


header('Content-type: Text/HTML; Charset=UTF-8');

main.php (included at the end of index.php)

<meta http-equiv="Content-Type" content="Text/HTML" />
<meta charset="UTF-8" />

The problem is that for some tables I have to manually insert the data, and this data is stored as is: with special characters, with accents, ñ, etc... And when I display this data in my website I can see that these characters
have replaced the special/accented characters.

So my question is: is there any way to store data as is (without replacing/converting special/accented characters) in mysql?

If I revert to
the data is displayed fine... So this keeps me wondering that there should be no problem with storing utf-8 characters as they are and there is some codification problem somewhere...

I'm using sqlyog community (with wine) and I read somewhere that sometimes the gui does not work correctly when you change some db/table configuration and the only way is the old way (running yourself the query), but I didn't tried this yet.

What do you think?

EDIT: If I encode accented data (as is) in json the word will be null.

Answer Source

The problem might stem from the fact that you're not using utf8mb4 in your MySQL column definition (at least you did not say what encoding you're using).

Here is an example of a MySQL table definition with a column that uses utfmb4:

CREATE TABLE `person` (
  `name` varchar(255) CHARACTER SET utf8mb4


Using the following table definition:

CREATE TABLE `person` (
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and the following PHP script:

$mysqli = new mysqli('localhost', 'username', 'password', 'database');

$mysqli->query("INSERT INTO `person` VALUES ('Altaïr Ibn-La\'Ahad')");

$query = "SELECT * FROM `person` LIMIT 1";
$result= $mysqli->query($query);

$person = $result->fetch_object();

    printf ("New Person has name %s.\n", $person->name);


when I insert "Altaïr Ibn-La'Ahad" into the database, the name is stored as is without changes. The script also prints the name without changes: "New Person has name Altaïr Ibn-La'Ahad."

I hope this helps you resolve your problem. Let me know if it does or doesn't.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download