Export MYSQL information to JSON

Asked

Viewed 1,877 times

0

Sorry for the ignorance, I don’t know anything about JSON.

I have a MYSQL database site that the client needs to export to JSON to integrate with an app.

Can you give me a light where to start?

Ex: table with information: name, email, id

Thank you!

php mysql json

  • You can start with the function json_encode() If you have more details edit the question.

  • you want to take the data cia database and using PHP to convert the data to json?

  • 1

    Dude you should make a select in the database, with PHP for example, and this return you send via json_encode()... and you can work it in the app or javascript..

  • I would sort of select to bring the results as if I were going to do with an echo, but instead echo uses json_enconde?

4 answers

2


To create the contents of a file JSON, first we create a array:

$meus_dados = array();

After we have a array created we must popular this array:

for ($i = 0; $i < 3; $i++) {
    $meus_dados[$i]['nome'] = "nome".($i + 1);
    $meus_dados[$i]['email'] = "email".($i + 1);
    $meus_dados[$i]['id'] = "id".($i + 1);
}

Now that we have ours array populated, we have to use the function json_encode to generate a JSON that array:

echo json_encode($meus_dados);

Upshot

[{"name":"name1","email":"Email1","id":"id1"},
{"name":"Nome2","email":"Email2","id":"id2"},
{"name":"name3","email":"email3","id":"id3"}]

  • before $meus_dados = array(); do I make the normal connection to the database? ($connects = new mysqli($servername, $username, $password, $dbname);)

  • Yes, I put the example this way to leave generic, but your query with mysqli will already return a array, you can give a json_encode straightforward.

  • I think it worked, but a doubt, this $i < 3, limits to 3 results? if yes, you can leave without limit, to bring everything?

  • In fact, it is limiting the amount of times we will popular our array. you are populating your array manually or using the array that the mysqli returns to you?

2

You can work as follows using PHP:

$query = "SELECT * FROM `table`";
$stmt->prepare($query);
$stmt->execute();
$stmt->fetchAll(\PDO::FETCH_ASSOC);

if($stmt){
    echo json_encode(["status"=>true,"msg"=>"Json enviado com sucesso!","data"=>$stmt]);exit;
}else{
    echo json_encode(["status"=>false,"msg"=>"Ocorreu um erro"]);exit;
}

1

THE JSON (http://json.org/) is a data format. It facilitates data exchange between different systems as it is simple to generate and recover. For your case, you need to generate a JSON output to be read by the system.

Recover the data the same way you would for any application. The difference will be in the output (or screen view), which will have the format application/json. In the simplest possible way:

$data = mysql_query('select * from table'); // aplique o seu metodo de recuperacao de dados
header('Content-type: application/json'); // cabecalho para o navegador saber que estah retornando um json
echo json_encode($data); // json_encode transforma qualquer tipo de objeto no formato json
exit;

0

If using PDO can look very elegant your return:

public function getCliente ( $idcliente ){
        require_once "class.connection.php";
        require_once "../model/class.cliente.php";
        $retorno = array();
        $this->connection = new connection();

        try{
            $query = "SELECT * FROM cliente WHERE CD_CLIENTE = :cliente";
            $stmt = $this->connection->prepare( $query );
            $stmt->bindValue( ":cliente", $idcliente, PDO::PARAM_INT );
            $stmt->execute();

            if( $row = $stmt->fetch( PDO::FETCH_ASSOC ) ){
                $retorno = array(
                   "id"    =>  $row['CD_CLIENTE'] ,
                   "nome"  =>  $row['NM_CLIENTE'] ,
                   "email" =>  $row['DS_EMAIL']
                )
            }

            $this->connection = null;
        }catch (PDOException $ex){
            echo "Erro: ".$ex->getMessage();
        }
        echo json_encode(array("cliente" => $retorno));
    }

The return will be more or less like this:

{
  "cliente" : [{
              "id"    : "1",
              "nome"  : "Leandro",
              "email" : "[email protected]"
              }]
}

  • You don’t understand?

Browser other questions tagged php mysql json

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