Return Json using Mysql

Asked

Viewed 854 times

1

I have 3 tables do the Inner Join in them , but I need to return a json, I only managed to return a simple select:

My Sql clause:

SELECT publicacao.codPublicacao as codpublicacao,
       usuario.nome as nomeusuario,
       publicacao.categoria as categoria,
       publicacao.endereco as endereco,
       publicacao.tipo as tipo,
       publicacao.descricao as descricao,
       cidade.nome as nomecidade, publicacao.contato as contato
FROM `publicacao`
inner join usuario on publicacao.codUsuario = usuario.codUsuario
inner join cidade on publicacao.codcidade = cidade.codcidade

Php: 

  $sql = "SELECT publicacao.codPublicacao as codpublicacao,usuario.nome as 
            nomeusuario, publicacao.categoria as categoria,
            publicacao.endereco as endereco, publicacao.tipo as tipo, 
            publicacao.descricao as descricao, cidade.nome as nomecidade,
            publicacao.contato as contato FROM `publicacao` inner join usuario on 
        publicacao.codUsuario = usuario.codUsuario inner join cidade on 
    publicacao.codcidade = cidade.codcidade"; 
                        $result = $conn->query($sql);
                    $encode = array();

                     while ($row = $result->fetch_assoc()) {
                       $encode[] = json_encode($row);

                    }
echo json_encode($encode);

?>
php mysql

  • This tried is API, search on json_encode http://php.net/manual/en/function.json-encode.php and there is another easy way to use GO program

  • 1

    Why don’t you use json_encode(); results (lines) returned from BD?

  • I used json_encode($vector) (I do not know if it is correct) but it does not return anything, the page is empty, but when I use a simple select, it appears json

  • You can provide the table structure together with data in an SQL file for testing?

  • Diego, it’s all right echo? Do not use while json_encode and for later, $encode = $row and echo json_encode($encode) to exit the screen result.

  • The database is here ->https://drive.google.com/open?id=0B-aEUv85JPz2ZzdiZFAtTnJYN2M

Show 1 more comment

1 answer

0

Why don’t you use it this way?

while ($row = $result->fetchAll()) {
    $encode[] = $row;
}
echo json_encode($encode);

Even in the previous way it was returning json correctly, but that’s enough.

If it doesn’t work out, look at the error log, maybe you’re bursting the memory of php.

  • I could not locate this function fetchAll(), so I searched it is obsolete error : Fatal error: Call to Undefined method mysqli_result::fetchAll()

  • I did it using PDO, but you can keep your fetch_assoc

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.