CODEWITHSUNDEEP
phpmysqlphpmyadmin
user3884753
user3884753

Reputation: 305

Show lingual text kept in varchar (utf8_unicode_ci) in MySQL server in Android app with JSON php script

I have gone through many posts on the same topic but still not able to figure out what is the solution. If you think that an available solution will work for me then you can direct me there too.

Now, I want to display text in my regional language (Hindi) inside my app. This text is stored in a table on my website server MySQL database. I am retrieving this data along with many more columns through an SQL query run through a php script in the form of a JSON.

When I am testing this JSON on JSONLint and also by taking a log of the json string received by the Async task inside the app, all the fields having Varchar type and 'utf8_unicode_ci' collation are showing as below with question marks. Other fields types are coming correctly. 

"?????? ??? ???? ?? ??????? ?? ????? ???? ?? 10 ?????"

Server details 

phpMyAdmin Version 4.7.3

PHP version: 5.6.30

Database 
Server type: MySQL
Server version: 5.6.38 - MySQL Community Server (GPL)
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)

Below is my table in the database. The difference in utf8 in the image is because I was trying if utf8mb4 works. The database is MyISAM type and thus there is no 'nvarchar' field type.

enter image description here

Below is my php script. I have two tables, Table 1 contains non-localized fields, Table 2 has the localized translation fields. 'hi' is my language code. The above table image is of the TableTranslation.

<?php

$host='localhost';
$hostname='*******';
$password='*******';
$db='*******';

$sql="SELECT TablePost.PostID,TableTranslation.PostCategory,TablePost.PostImage,TableTranslation.PostTitle,TableTranslation.PostBrief,
TableTranslation.PostLink,TablePost.IsShort,TablePost.IsSeenBy 

FROM TablePost LEFT JOIN TableTranslation ON TablePost.PostID = TableTranslation.PostID 
WHERE TableTranslation.Language LIKE 'hi';";

$con=mysqli_connect($host,$hostname,$password,$db);

$result = mysqli_query($con,$sql);

$response = array();

while($row=mysqli_fetch_array($result)){
array_push($response, array(
    "PostID" => $row[0],
    "PostCategory" => $row[1],
    "PostImage" => $row[2],
    "PostTitle" => $row[3],
    "PostBrief" => $row[4],
    "PostLink" => $row[5],
    "IsShort" => $row[6],
    "IsSeenBy" => $row[7]));
}

echo json_encode(array("server_response"=>$response));

mysqli_close($con);

?>

Below is the JSON response I am getting. 

{
"server_response": [{
    "PostID": "1",
    "PostCategory": "??????????",
    "PostImage": "http:\/\/websitename.com\/image.png",
    "PostTitle": "?????? ??? ???? ?? ??????? ?? ????? ???? ?? 10 ?????",
    "PostBrief": "???? ?? ????????? ?? ???-??? ?????? ?? ??? ???????? ???? ???? ?? ?? ??? ????? ??? ??? ?????? ????? ???? ???? ??? ?? ?? ???? ?? ??????? ?? ????? ???? ?? ??? ???????? ????? ???? ????",
    "PostLink": "http:\/\/websitename.com\/post_link\/",
    "IsShort": "0",
    "IsSeenBy": "98"
}]
}

How do I correct it? Will really appreciate any help here.

Upvotes: 0

Views: 152

Answers (2)

Omkar
Omkar

Reputation: 3100

add line mysqli_set_charset( $con, 'utf8'); after mysqli_connect() like below code

    <?php

$host='localhost';
$hostname='*******';
$password='*******';
$db='*******';

$sql="SELECT TablePost.PostID,TableTranslation.PostCategory,TablePost.PostImage,TableTranslation.PostTitle,TableTranslation.PostBrief,
TableTranslation.PostLink,TablePost.IsShort,TablePost.IsSeenBy 

FROM TablePost LEFT JOIN TableTranslation ON TablePost.PostID = TableTranslation.PostID 
WHERE TableTranslation.Language LIKE 'hi';";

$con=mysqli_connect($host,$hostname,$password,$db);

mysqli_set_charset( $con, 'utf8');  //change here.

$result = mysqli_query($con,$sql);

$response = array();

while($row=mysqli_fetch_array($result)){
array_push($response, array(
    "PostID" => $row[0],
    "PostCategory" => $row[1],
    "PostImage" => $row[2],
    "PostTitle" => $row[3],
    "PostBrief" => $row[4],
    "PostLink" => $row[5],
    "IsShort" => $row[6],
    "IsSeenBy" => $row[7]));
}

echo json_encode(array("server_response"=>$response));

mysqli_close($con);

?>

also try to add collation utf8_general_ci to PostTitle and PostBrief like

enter image description here

Upvotes: 1

Ramesh sambu
Ramesh sambu

Reputation: 3539

I had a similar issue with Arabic and Hindi i solved like below.You can solve this issue in MySQL side.

In the table structure, you have to change collation utf8_bin 

"PostID" - `utf8_bin`   instead of `latin`,
"PostCategory" - `utf8_bin`  and so on.

Upvotes: 0

Related Questions