Json encoding with accented characters

Question

i'm trying to get data from MySql Database. This is my code.

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT * FROM Quotes WHERE Id=2";

if (mysqli_connect_errno()) die('Could not connect: ' . mysql_error());

$return_arr = array();
if ($result = mysqli_query($conn, $sql)) {
    while ($row = mysqli_fetch_assoc($result)) {
        $row_array['Id'] = $row['Id'];
        $row_array['Quote']= $row['Quote'];
        $row_array['Author'] = $row['Author'];
        $row_array['Category'] = $row['Category'];
        $row_array['Likes'] = $row['Likes'];
        array_push($return_arr, $row_array);
    }
}
echo json_encode($return_arr, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP | JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

mysqli_close($conn);

On the web page nothing appears to me, I found the problem is due to the accented characters like "è or "ò". In fact when i try this:

<?php
echo "Result 1";
$return_arr = array();
if ($result = mysqli_query($conn, $sql)) {
    while ($row = mysqli_fetch_assoc($result)) {
        $row_array['Id'] = $row['Id'];
        $row_array['Quote']= $row['Quote'];
        $row_array['Author'] = $row['Author'];
        $row_array['Category'] = $row['Category'];
        $row_array['Likes'] = $row['Likes'];
        array_push($return_arr, $row_array);
    }
}
echo json_encode($return_arr, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP | JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

echo " Result 2 ";
$sql2 = "SELECT * FROM Quotes WHERE Id=2";

if (mysqli_connect_errno()) die('Could not connect: ' . mysql_error());

$return_arr2 = array();
if ($result2 = mysqli_query($conn, $sql2)) {
    while ($row = mysqli_fetch_assoc($result2)) {
        $row_array['Id'] = $row['Id'];
        $row_array['Quote']= $row['Quote'];
        $row_array['Author'] = $row['Author'];
        $row_array['Category'] = $row['Category'];
        $row_array['Likes'] = $row['Likes'];
        array_push($return_arr2, $row_array);
    }
}

echo json_encode($return_arr2, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP | JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

mysqli_close($conn);
?>

I get this result on the screen: Result 1[ { "Id": "1", "Quote": "My Simple Text", "Author": "Me", "Category": "X", "Likes": "0" } ] Result 2

I'm using php 7.2 and the latest version or MySql. This is how I configured my database Database structure Database simple Data for testing

UPDATE: I changed all the encodings in utf8_bin but it had no effect. Database encoding Columns encoding Thanks for your attention.

I changed all the encodings in utf8_bin but it seems to have no effect. — xDazuk, Apr 03 '18 at 22:41
Do You use `mysqli_set_charset($conn, 'utf8');` ? https://secure.php.net/manual/en/mysqli.set-charset.php — Michas, Apr 03 '18 at 23:30

score 0 · Answer 1 · answered Apr 03 '18 at 22:55

0

Try saving all your files in UTF-8 NO BOM format. For example with the notepad as on the following screenshot:

Does this solve ? I already had this problem ... Worked for me

answered Apr 03 '18 at 22:55

Joel

192
12

score 0 · Answer 2 · answered Apr 03 '18 at 23:25

I solved my problem. I modified my code in this way, thus obtaining the desired result, I hope it will be useful for those with the same problem, the whole database has been encoded in utf8_bin.

$return_arr2 = array();
if ($result2 = mysqli_query($conn, $sql2)) {
    while ($row = mysqli_fetch_assoc($result2)) {
        $row_array['Id'] = $row['Id'];
        $row_array['Quote']= utf8_encode($row['Quote']);
        $row_array['Author'] = $row['Author'];
        $row_array['Category'] = $row['Category'];
        $row_array['Likes'] = $row['Likes'];
        array_push($return_arr2, $row_array);
    }
}

echo json_encode($return_arr2, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP | JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);

I force the utf8 encoding with "utf8_encode". Now the result is:

Result 1[ { "Id": "1", "Quote": "My Simple Text", "Author": "Me", "Category": "X", "Likes": "0" } ] Result 2 [ { "Id": "2", "Quote": "My Simplè Text with è or à", "Author": "Me", "Category": "X", "Likes": "0" }

I think that i can use only JSON_UNESCAPED_UNICODE but i prefer to keep also the others.

It looks Your connection with database is in CP-1252. – Michas Apr 03 '18 at 23:35 — Michas, Apr 03 '18 at 23:35

Json encoding with accented characters

2 Answers2