0

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.

SNos
  • 3,430
  • 5
  • 42
  • 92
  • "Showing in the mysql database" - how exactly you look at the data? – Dekel Jan 03 '17 at 00: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 Jan 03 '17 at 00:27
  • I am looking with phpMyAdmin – SNos Jan 03 '17 at 00:27
  • 1
    Try to add `mysqli_query($conn, "SET NAMES 'utf8'");` right after you did the connection. – Dekel Jan 03 '17 at 00:28
  • 1
    (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 Jan 03 '17 at 00:31
  • @Dekel It worked.. Thank you so much !! – SNos Jan 03 '17 at 00:31
  • you are welcome :) care to vote the answer as well? – Dekel Jan 04 '17 at 00:07
  • See "Mojibake" in http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored – Rick James Apr 09 '17 at 04:10

1 Answers1

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.

Dekel
  • 60,707
  • 10
  • 101
  • 129