PHP: json_encode and json_decode with UTF-8

Question

I have the following array:

Array
(
    [1] => Array
        (
            [time] => 07:30
            [event] => Celebrity Organ Recital &acirc;€“ Sophie-V&Atilde;&copy;ronique Cauchefer-Choplin
        )
)

(original event string is: "Celebrity Organ Recital – Sophie-Véronique Cauchefer-Choplin", I used htmlentities with ENT_QUOTES)

When I use json_encode the event string is returned as NULL, and it's saved as an empty string in MySQL.

If I don't use htmlentities. I will get this in my database: "Celebrity Organ Recital u2013 Sophie-Vu00e9ronique Cauchefer-Choplin". I used many methods but I still can't convert this string back to its original.

I really need some helps on this, I hope you could give me a solution to encode an UTF-8 string like the above one in json, then save it to MySQL, and then decode back to its original. I searched for a while but still can't find a solution.

Thank you so much!

"I will get this in my database" That's because you're saving it wrong. — Ignacio Vazquez-Abrams
– Ignacio Vazquez-Abrams, Commented Apr 26, 2012 at 4:52
what you get in .sql file if you export that specific row from phpmyadmin? — Nimit Dudani
– Nimit Dudani, Commented Apr 26, 2012 at 4:56
@nDudani: This i what i get: {"1":{"time":"07:30","name":"Celebrity Organ Recital u2013 Sophie-Vu00e9ronique Cauchefer-Choplin"}}. If I don't use htmlentities. — Hung Tran
– Hung Tran, Commented Apr 26, 2012 at 5:24

Sergey Ratnikov · Accepted Answer · 2012-04-26 12:45:54Z

It seems to me that you don't care about sanitize values in sql queries http://php.net/manual/en/function.mysql-real-escape-string.php

Simple example: We have table with structure:

CREATE TABLE `test` (
    `text` VARCHAR(1024) NULL DEFAULT '0'
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB

And PHP script

   header("Content-type:text/html;charset=utf8");
    $ar = array
        (
            1 => Array
                (
                    'time' => '07:30',
                    'event' => 'Celebrity Organ Recital – Sophie-Véronique Cauchefer-Choplin'
                )
        );

    $mysqli = new mysqli('localhost','root', '', 'test');

    $mysqli -> query("INSERT INTO `test` (`text`) VALUES ('" . json_encode($ar) . "')"); // we not escape characters like \, ", '

// now we use mysqli::real_escape_string
    $mysqli -> query("INSERT INTO `test` (`text`) VALUES ('" . $mysqli -> real_escape_string(json_encode($ar)) . "')"); // here we escape characters

    $mysqli_result = $mysqli -> query("SELECT * FROM `test");
    while($result = $mysqli_result -> fetch_assoc()){
        var_dump(json_decode($result["text"],true));
    }

The result of var_dump is:

array
  1 => 
    array
      'time' => string '07:30' (length=5)
      'event' => string 'Celebrity Organ Recital u2013 Sophie-Vu00e9ronique Cauchefer-Choplin' (length=68)

array
  1 => 
    array
      'time' => string '07:30' (length=5)
      'event' => string 'Celebrity Organ Recital – Sophie-Véronique Cauchefer-Choplin' (length=63)

The second var_dump is normal

Collectives™ on Stack Overflow

PHP: json_encode and json_decode with UTF-8

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related