Create Json for PHP based on Mysql

Question

I wonder if anyone finds any error in my code because I’m trying to create a Json with PHP but it only returns NULL.

My Host server uses PHP 5.6

 <?PHP    
    $host = "...";    
    $usuario = "...";    
    $senha = "...";    
    $banco = "...";    

    $database = mysqli_connect($host,$usuario,$senha,$banco);    

$result=mysqli_query("SELECT * FROM tbconsultas");

$i=0;
while($row=mysqli_fetch_array($result)) { 
  $response[$i]['url']  = $row['url']; 
  $response[$i]['title']= $row['title'];
  $data['posts'][$i] = $response[$i];
  $i=$i+1;
} 

$json_string = json_encode($data);

$file = 'file.json';
file_put_contents($file, $json_string);
?> 

Answer 1

I don’t have PHP lower than 7 on my machine, so I can’t test your code to tell you punctually what the error is, but using Mysqli you can do something like:

// Conecta com o banco de dados:
$mysqli = mysqli_connect("localhost", "root", "", "sopt");

// Seleciona todos os registros da tabela:
$result = mysqli_query($mysqli, "SELECT * FROM tbconsultas");

// Retorna todos os registros:
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);

// Escreve o resultado JSON em arquivo:
file_put_contents("file.json", json_encode($data));

---

If it is not possible to use the function mysqli_fetch_all as shown above, just make a repeat loop iterating over all the records and storing in a array.

// Lista que armazenará todos os registros:
$rows = [];

// Percorre todos os registros:
while ($row = mysqli_fetch_assoc($result)) {

    // Adiciona o registro na lista:
    array_push($rows, $row);

}

At the end, just convert the list to JSON:

$json = json_encode($rows);