PHP - insert Unicode data into MYSQL formatted properly

Question

I am inserting some data from a sjson file into mysql database. Some of the data is formatted using unicode.

When I echo the data on the browser is showing fine however, it is inserted in the database not formatted properly.

In the database I am using as Collation utf8_unicode_ci.

JSON data: anch\u2019io, sar\u00e0

Showing in the Browser: anch'io, sarà

Showing in the mysql database: anchâ€™io, sarÃ

How can I inserted in the database the text properly formatted?

PHP

$getUrl = "https://example.com/79809000.json";
$json_level = file_get_contents($getUrl);
$data_level = json_decode($json_level);

$text = $data_level->{"text"};

mysqli_query($conn, "INSERT INTO `70_level`(`text`) VALUES ('$text')");

I have tried to use addslashes, htmlentities but it does not work.

"Showing in the mysql database" - how exactly you look at the data? — Dekel
– Dekel, Commented Jan 3, 2017 at 0:27
You should make sure to use the correct connection collate both in your PHP code and in the application you use to view the data — Dekel
– Dekel, Commented Jan 3, 2017 at 0:27
Try to add mysqli_query($conn, "SET NAMES 'utf8'"); right after you did the connection. — Dekel
– Dekel, Commented Jan 3, 2017 at 0:28
(Note the this will only affect the new data you will insert, but it will also affect the data you select, so if you also show unicode data - you might need to re-insert it to the database). — Dekel
– Dekel, Commented Jan 3, 2017 at 0:31

Dekel · Accepted Answer · 2017-01-03 00:34:05Z

1

You are probably not using utf8 as your character set connection/collation connection.

The easiest way to fix this will be to use

$conn = mysqli_connect(...);
mysqli_query($conn, "SET NAMES 'utf8'");

The SET NAMES query should be the first query you run, so make sure you put it right after your mysqli_connect function.

Note that this change will affect all the data, so if you already have data in the database - you will need to re-insert it using the new (utf8) charset.

answered Jan 3, 2017 at 0:34

Dekel

62.9k12 gold badges109 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP - insert Unicode data into MYSQL formatted properly

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related