Php returns null JSON when it has accent in Mysql

Question

Hello, I have the following code in php:

<?php
   $con=mysqli_connect("localhost","user","senha","banco");
   $parametro = $_GET["parametro"];
   if (mysqli_connect_errno()){ echo "Failed to connect to MySQL: " .mysqli_connect_error(); }
   $sql = "SELECT * FROM " . $parametro;

   if ($result = mysqli_query($con, $sql))
   {
        $resultArray = array();
        $tempArray = array();

    while($row = $result->fetch_object())
    {
        $tempArray = $row;
        array_push($resultArray, $tempArray);
    }
    echo json_encode($resultArray);
}
mysqli_close($con);
?>

It returns a JSON with a table of the Mysql server whose name was passed by the parameter, but when it has an accent or a "ç" for example it returns null Mysql is accepting the accent, I can check it by phpmyadmin, the problem is even in php. For a table cities it returns this:

[{"id":"1","nome":"Porto Alegre","estado":"RS"},{"id":"2","nome":"Rio de janeiro","estado":"RJ"},{"id":"3","nome":null,"estado":"SP"}]

Where São Paulo should be he returns null

I have tried using json_encode with JSON_UNESCAPED_UNICODE constant to encode characters literally instead of escaping them.

echo json_encode($resultArray, JSON_UNESCAPED_UNICODE);

but this error appears: Warning: json_encode() expects exactly 1 parameter, 2 given in /home/diretorio/public_html/busca.php on line 24

how to solve this?

Answer 1

Simplest solution found is to add these two lines:

$str = str_replace('\u','u',$decoded);
$strJSON = preg_replace('/u([\da-fA-F]{4})/', '&#x\1;', $str);

Stay tuned to your PHP version, because I use 5.5 and in the tests I did not have this problem! My tests were like this:

echo json_encode($json, JSON_UNESCAPED_UNICODE);

[{"id":"1","name":"Porto Alegre","state":"RS"},{"id":"2","name":"Rio of January","state":"RJ"},{"id":"3","name":"SANE Paulo","state":"SP"}]

echo json_encode($json);

[{"id":"1","name":"Porto Alegre","state":"RS"},{"id":"2","name":"Rio of January","state":"RJ"},{"id":"3","name":"S u00e3o Paulo","state":"SP"}]

Answer 2

Try something like that:

<?php
       $con=mysqli_connect("localhost","user","senha","banco");
       $parametro = $_GET["parametro"];
       if (mysqli_connect_errno()){ echo "Failed to connect to MySQL: " .mysqli_connect_error(); }
       $sql = "SELECT * FROM " . $parametro;

       if ($result = mysqli_query($con, $sql))
       {

        while($row = $result->fetch_object())
        {
            foreach($row as $key => $col){
               $col_array[$key] = utf8_encode($col);
            }
            $row_array[] =  $col_array;

        }
        echo json_encode($row_array);
    }
    mysqli_close($con);
    ?>

Answer 3

A line before the echo json_encode($resultArray), put that:

$resultArray = array_map('utf8_encode', $resultArray);

Answer 4

Before returning you call a function to convert the array to utf-8

$result = converteArrayParaUtf8($result); //chama a função
return json_encode($result); //retorna

function:

function converteArrayParaUtf8($result){
    array_walk_recursive($result, function(&$item,$key){
         if (!mb_detect_encoding($item, 'utf-8', true)) {
                $item = utf8_encode($item);
            }
    });
    return $result;
}

Answer 5

Friend, you can use the mb_convert_encoding function to convert the encoding

//In this case, assuming the standard is in ISO-8859-1 and you want UTF-8 mb_convert_encoding($utf8, 'UTF-8', 'ISO-8859-1');