Json PHP and Mysql

Question

I am working in a personal project with an open database, and I need a JSON file from a MYSQL query, I am a few days trying to solve this problem and I am very difficult, because I can not get the name of my data. Example: I make a query to list all states, I can get json with the ID, UF, Region, when I put the name field, returns me nothing and gives the error Resource Interpreted as Document but transferred with MIME type application/json: "http://localhost/teste.php". My goal is to take all the query data and return a JSON to deal with Geocharts.

<?php

$con = mysqli_connect("localhost","root","3919223","DatasusNew") or die("Error " . mysqli_error($con));
mysqli_query('SET NAMES utf8;');
$var = array();
$sql = "SELECT * FROM estados";
$result = mysqli_query($con, $sql);

foreach ($result as $row) {
    $return[] = [ 
        'nome' => $row['nome'],
        'idEstado' => $row['idEstado'],
        'uf' => $row['uf'],
        'regiao' => $row['regiao']

    ];
}
$dbh = null;

header('Content-type: application/json');
echo json_encode($return);

?>

Has anyone been through this problem? Or any tips?

Answer 1

Amigo, I did it!! The final code was... Thanks

   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, SON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
}
mysqli_close($con);
?> `

You’re returning me like this: [{"idEstado":"11","name":"Rondônia","Uf":"RO","regiao":"Norte"}, Now I’ll have to process this data in JS... Thanks!

Answer 2

Do the following: use mysqli_fetch_assoc(), it returns the results associated with the table name, I think it looks something like this:

    <?php

        $con = mysqli_connect("localhost","root","3919223","DatasusNew") or die("Error " . mysqli_error($con));
    mysqli_query('SET NAMES utf8;');
        $var = array();
        $sql = "SELECT * FROM estados";
        $result = mysqli_query($con, $sql);
        $rows = array();
        while($each = mysqli_fetch_assoc($result)){
            $rows[] = $each;
        }
        header('Content-type: application/json');
        echo json_encode($rows);
     ?>

Answer 3

Answer:

$lista_json = array("objects_array" => array());
foreach($result as $row){
     $obj = array (
         "nome" => utf8_encode($row['nome']),
         "idEstado" => $row['idEstado'],
         "uf" => utf8_encode($row['uf']),
         "regiao" => utf8_encode($row['regiao'])
     );

     array_push($lista_json["objects_array"], $obj);
}

header('Content-type: application/json');
echo json_encode($lista_json);

Worked?

References:

Convert json object in PHP

PHP: utf8_encode