0

I use json to echo variables from my database. However ë display as ■.

In an attempt to correct this I have done this:

With no luck. Any ideas how to fix.

EDIT:

if (isset($_GET['term'])){
$return_arr = array();

try {
    $conn = new PDO("mysql:host=".DB_SERVER.";port=8889;dbname=".DB_NAME, DB_USER, DB_PASSWORD);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->prepare('SELECT School FROM Schools WHERE School LIKE :term');
    $stmt->execute(array('term' => '%'.$_GET['term'].'%'));

    while($row = $stmt->fetch()) {
        $return_arr[] =  $row['School'];
    }

} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}


/* Toss back results as json encoded array. */

echo mb_detect_encoding($return_arr, 'auto');

}
CharlieT
  • 373
  • 4
  • 26
  • 2
    Have you considered the `JSON_UNESCAPED_UNICODE` flag? See http://php.net/manual/en/function.json-encode.php. – elixenide Nov 11 '17 at 01:42
  • I did try, but still the same result – CharlieT Nov 11 '17 at 01:45
  • echo mb_detect_encoding('■', 'auto'); returns that it's already UTF-8. JSON_UNESCAPED_UNICODE wont help you as "\xc3\xa9" is the unescaped unicode for é. I suspect the problem is in your database character set – miknik Nov 11 '17 at 02:28
  • Its not the Database. I have tested by writing a test.php to echo the variable without json. Without JSON it displayed correctly. – CharlieT Nov 11 '17 at 02:31
  • It could still be the database. What is the result if you get the variable from the database and then run echo mb_detect_encoding($variable, 'auto') – miknik Nov 11 '17 at 02:36
  • @miknik Thanks. Just tried. That. No luck. I also updated my question with ne code. Still not working – CharlieT Nov 11 '17 at 03:06
  • You misunderstand. Do the test where you get the variable from the database without json encoding it, so that it will display correctly. Then run echo mb_detect_encoding($variable, 'auto') and the result will tell you how its encoded – miknik Nov 11 '17 at 04:09

2 Answers2

1

First, take a look if your table is actually utf8 encoded. It should look something like this:

CREATE TABLE `Schools` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `School` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then connect to your database like this:

$dsn = 'mysql:dbname=test;host=127.0.0.1;port=3306;charset=utf8';
$user = 'root';
$password = 'password';
$conn = new PDO($dsn, $user, $password, [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"]);

Finally use JSON_UNESCAPED_UNICODE:

echo json_encode($row, JSON_UNESCAPED_UNICODE);

This should do the trick.

UPDATE

This worked on PHP7.1 on the table I provided above:

if (isset($_GET['term'])) {
    $return_arr = array();

    try {
        $conn = new PDO("mysql:host=127.0.0.1;port=8101;dbname=test;charset=utf8", $user, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stmt = $conn->prepare('SELECT School FROM Schools WHERE School LIKE :term');
        $stmt->execute(array('term' => '%'.$_GET['term'].'%'));

        $return_arr = [];
        while($row = $stmt->fetch()) {
            $return_arr[] =  $row['School'];
        }

    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }


    /* Toss back results as json encoded array. */

    echo json_encode($return_arr, JSON_UNESCAPED_UNICODE);

}
zstate
  • 1,995
  • 1
  • 18
  • 20
  • Its not the Database. I have tested by writing a test.php to echo the variable without json. Without JSON it displayed correctly. – CharlieT Nov 11 '17 at 02:32
  • @CharlesTester you still need to set the charset when you connect to the database, without charset it won't work. See my updated answer. – zstate Nov 11 '17 at 03:15
0

1) Use JSON_UNESCAPED_UNICODE in json_encode.

2) Make sure that data storage, data access, output and input in UTF-8 charset (UTF-8 all the way through).

camelsWriteInCamelCase
  • 1,655
  • 1
  • 10
  • 15
  • Its not the Database. I have tested by writing a test.php to echo the variable without json. Without JSON it displayed correctly. – CharlieT Nov 11 '17 at 02:32