Content Type Character Set in PHP Header of JSON Data for utf8_unicode_ci - Ajax Call

Question

I'm using utf8_unicode_ci for a Column in MYSQL Table to store TAMIL Language Characters.

I'm Implementing an AngularJS Project, in that I'm calling the PHP Service, the return type is a JSON Data. I Can't able to get the actual Characters, instead of that I'm getting ?????????.

My PHP Sample Source Code:

<?php

header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");

$outp = "";

$sql_select = "";
$sql_select .= "SELECT * FROM poll_quest WHERE qid = $nqid LIMIT 1 ";
$bQuery = mysql_query($sql_select, $link);
while ($bRow = mysql_fetch_array($bQuery)) {

    $qflag = true;

    $outp .= '{ "ID": ' . $bRow['qid'] . ',';
    $outp .= '"Ans":"' . $bRow['ans_tam'] . '" }';
}

$outp ='{"records":['.$outp.']}';
echo($outp);

?>

My Tamil Words are

மோசம்
மோசமாக இல்லை
நன்று
மிக நன்று

The MySQL Table Structure Snapshot:

The MySQL Table Data Snapshot:

The Output JSON Data Snapshot:

{
   "records":[
      {
         "ID":"1",
         "Ans":"??. ????????"
      },
      {
         "ID":"2",
         "Ans":"??. ?????????"
      },
      ........
      {
         "ID":"5",
         "Ans":"??. ??????????"
      }
   ]
}

Kindly assist me, how to get the actual characters in the Response JSON...

If you just print what you get ? I mean... using echo with header("Content-Type: text/html; charset=utf-8"); — boctulus, Mar 19 '16 at 03:45
@Boctulus - Refer the The Output JSON Data Snapshot, I got this in the Browser. — B.Balamanigandan, Mar 19 '16 at 03:58
Try using mysqli_set_charset($conn,'utf8') after connect to your DB — boctulus, Mar 19 '16 at 11:16

score 2 · Accepted Answer · answered Mar 19 '16 at 07:02

First things first the MySQL driver i.e. mysql_* has been deprecated in favour of PDO or MySQLi a while back. With that said, lets move on.

The answer to you need to set the character set on the connection i.e. you will need to do the following to make it work -

<?php
...
$link = mysqli_connect(...);
mysql_set_charset('utf8', $link); // This is your answer
...

For future reference, I have reworked the above code to use MySQLi to help you out a bit -

<?php
// NOTE : I prefer using PDO but I have used mysqli for this example
// NOTE : The following code HAS NOT BEEN TESTED!

header('Content-Type: application/json; charset=UTF-8');

// Connection information
$link = new mysqli($host, $user, $password, $dbname);

$link->set_charset('utf8'); // This is important or mysql_set_charset('utf8', $link) if you insist on using mysql_*

// Check connection
if (mysqli_connect_errno()) {
    printf('Connect failed: %s\n', mysqli_connect_error());

    exit();
}

$outp = '';

// Always use prepared statements!!
if ($stmt = $link->prepare('SELECT qid, ans_tam FROM poll_quest WHERE qid = ? LIMIT 1')) {
    $stmt->bind_param('i', $nqid); // user 's' if this is a string for the first parameter

    $stmt->execute();

    $stmt->bind_result($qid, $ans_tam);

    while ($stmt->fetch()) {
        $qflag = true;

        $outp .= '{ "ID" : ' . $qid . ', "Ans" : "' . $ans_tam . '" }';
    }

    $stmt->close();
}

echo('{"records":[' . $outp . ']}');

score 1 · Answer 2 · edited May 23 '17 at 12:23

I believe the only thing you miss is

mysql_set_charset('utf8');

even though you have set utf8_unicode_ci for tables or fields, the connection to your database is not necessarily automatically set to use utf8 character encoding.

NB: You should consider using utf8mb4_unicode_ci instead of utf_unicode_ci, and you should really consider to set the caracter set and collation on database (at least table) level not field level (I see you have latin1_swedish_ci as default indicating your entire database is latin1_swedish_ci.

Content Type Character Set in PHP Header of JSON Data for utf8_unicode_ci - Ajax Call

2 Answers2