php mysql query results utf8mb4_bin encoding to utf-8 for html

Question

Mysql table looks like this:

The actual contents are like this:

I tried these: https://stackoverflow.com/a/23179613/13865853

in model.php

 if (mysqli_num_rows($result) > 0) {
        //iterate
        while($row = mysqli_fetch_assoc($result)){
            //get username
            $disease = mb_convert_encoding($row['disease'], "UTF-8", "auto");
            $food = mb_convert_encoding($row['food'], "UTF-8", "auto");
            $en_name = mb_convert_encoding($row['en_name'], "UTF-8", "auto");
            $health_effect = mb_convert_encoding($row['healthEffect'], "UTF-8", "auto");
            $metabollite = mb_convert_encoding($row['metabollite'], "UTF-8", "auto");
            //$citation = $row['citation'];
            $next_row = array("Disease"=>$disease, "Food"=>$food,
                "Name"=>$en_name, "Health Benefits"=>$health_effect, "Metabollite"=>$metabollite);
                //"Sources"=>$citation);
            $matches_arr[] = $next_row;
        }
    }
    $utfArr = array_map("utf8_encode", $matches_arr);//this does not work for associate arrays I think

back in controller.php I tried again

 //encode in UTF8
 foreach($matches_arr as $col => $val){
 $utf8_val = mb_convert_encoding($val, "UTF-8", "auto");
 $matches_arr[$col] = $utf8_val;
 }
//send it back to view
header('Content-Type: application/json');
$disease_json = json_encode($matches_arr, JSON_UNESCAPED_UNICODE);
echo $disease_json;

but it outputs question marks for table columns in Japanese:

I saw this: https://stackoverflow.com/a/30826067/13865853 But I think my case is different because mysql database is already encoded in utf8mb4_bin and not an obscure Japanese encoding.

Anyways from that answer I tried putting this tag at the top of controller.php and model.php

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />"

and check that my view.php has the charset:

    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="">
    <meta name="author" content="Mark Otto, Jacob Thornton, and Bootstrap contributors">
    <meta name="generator" content="Jekyll v4.1.1">
    <title>Dashboard Bioactive Compounds</title>
    <!-- boostrap comes first -->
    <link rel="stylesheet" href="css/bootstrap.css">
    <meta charset="utf-8">

Actually it had it twice.
I saw that the query result in model.php is already just question marks in phpstorm debugger console when I inspect the query result variable.
How can I fix this to show Japanese on the view page?

score 1 · Accepted Answer · answered Nov 11 '20 at 20:23

MySQL's CHARACTER SET utf8mb4 is equivalent to the outside world's UTF-8. Hence no conversion should be necessary.

The question marks say

The bytes to be stored are not encoded as utf8/utf8mb4. Fix this.
The column in the database is not CHARACTER SET utf8 (or utf8mb4). Fix this. (Use SHOW CREATE TABLE.)
Also, check that the connection during reading is UTF-8.

See Trouble with UTF-8 characters; what I see is not what I stored

That link also provides some debugging info.

Note, the problem was probably on the INSERT side -- meaning that the data is lost. However, the display seems to indicate that the data is correctly stored. Possibly the redundant mb_convert_encoding calls are causing the problem.

php mysql query results utf8mb4_bin encoding to utf-8 for html

1 Answers1