Android Question Mysql PHP Encoding problem

[khwarizmi]

Hi all
I have a mysql database linked by php, this is the config.php file:

<?php
$username="username";
$password="password";
$host="localhost";
$database="database";
$con = mysqli_connect($host,$username,$password, $database) or die(mysqli_error($con));
$mysqli = new mysqli("localhost", $username, $password, $database);
$mysqli->set_charset("utf8mb4");
?>

this if the php file:

<?php
header("Content-type: application/json; charset=utf-8");
include ('../includes/config.php');
$q = "SELECT * FROM GeneralInfo";
$result = $con->query($q);
       $rows = array();
        while($r = mysqli_fetch_assoc($result)) {
                 $rows[] = $r;
            }
if($rows ==null)
  print(json_encode(''));
else
print(json_encode($rows));
?>

and this is my B4A code:

Dim job2 As HttpJob
    job2.Initialize("", Me)
    job2.Download("https://mywebsite/GeneralInfo/getinfo.php" )
    Wait For (job2) JobDone(job2 As HttpJob)
    Dim res As String
    res = job2.GetString
    res = "[" & res & "]"
    Log(res)

the encoding of the database and all the tables and fields is utf-general-ci but question marks appeared in places of the Arabic letters in res string. where is the problem?

thanks.

 

[Sandman]

all the tables and fields is utf-general-ci

You probably mean utf8-general-ci. Short story: utf8 on MySQL is crap, it's not really utf8 as the rest of the world knows it. They did it like this way back because of performance reasons, and we all have to suffer for it now. The good news is that we now instead have utf8mb4, which is true utf8 in MySQL.

Here is one article on it:

MySQL utf8 vs utf8mb4 - What's the difference between utf8 and utf8mb4?

This post's contentThe Story of UTF8 VS UTF8MB4RecommendationHow to convert utf8 to utf8mb4 in MySQL? The Story of UTF8 VS UTF8MB4 I once got a call from the support team, saying that one of our customers reported that the application fails to save data in one of our business-critical features...

www.eversql.com

Given this you should be able to find more relevant info on the net.

 

[khwarizmi]

Unfortunately the problem is still there.
I tried utf8mb4 -general-ci and utf8mb4-unicode-ci the situation is still the same.


I use this php code to get encodig, and alway it give: latin:

$mysqli = new mysqli("localhost", "username", "password", "database");
printf("character set: %s\n", $mysqli->character_set_name());

 

[khwarizmi]

I noticed that the database server charset is latin, is it the problem? How can I solve it?

 

[Sandman]

mysql change database server charset at DuckDuckGo

DuckDuckGo. Privacy, Simplified.

duckduckgo.com

 

[aeric]

I think after change the database collation, you also need to change or make sure all the varchar columns or the one column you concern are/is using utf8mb4. Then update all the values again with the new encoding or else the values are still stored in old format or less bytes.

 

[khwarizmi]

All tables and fields have utf8mb4-unicode-ci encoding.

 

[Sandman]

Then update all the values again with the new encoding or else the values are still stored in old format or less bytes.

Partially correct. I did a big update earlier this year from utf8 to utf8mb4 without doing that - no need to restore anything.

 

[aeric]

I encountered this issue previously.
https://www.b4x.com/android/forum/t...mysql-api-server-key-token.126081/post-795321

Edit: The problem is the wink-tongue emoticon is 4 bytes. ?

 

[khwarizmi]

Based on all the previous responses, the coding was done correctly, is there a possibility that the problem is somewhere else?

 

[aeric]

I would use Postman to test the output from the PHP first. Since this is a GET request, you may also just use a web browser to open the URL

https://mywebsite/GeneralInfo/getinfo.php

 

[khwarizmi]

Postman also gave the same result.

 

[aeric]

mysqli not setting charset to utf8mb4

Problem found The problem seems to be that $mysqli->set_charset() is not accepting `utf8mb4' as a valid encoding (just as I "speculated" in the first update). MySQL version is 5.5.41 and PHP ve...

stackoverflow.com