Php code to get json data from mysql database

Question

Iam trying to create a JSON object out of my SQL data in PHP. How to do that? This is my approach, which does not work so far.

<?php header("Access-Control-Allow-Origin: *"); header('Content-Type: text/html; charset=utf-8'); $dns = "mysql:host=localhost;dbname=afreen"; $user = "root"; $pass = ""; try { $conn = new PDO($dns, $user, $pass); if (!$conn) { echo "Not connected"; } $query = $conn->prepare('SELECT id, name, salary from afreen'); $query->execute(); $registros = "["; while ($result = $query->fetch()) { if ($registros != "[") { $registros .= ","; } $registros .= '{"id": "' . $result["id"] . '",'; $registros .= '"name": "' . $result["name"] . '"}'; $registros .= '"salary": "' . $result["salary"] . '"}'; $registros .= "]"; echo $registros; } catch (Exception $e) { echo "Erro: " . $e->getMessage(); } ?>` 

Answer 1

Why dont you try the json_encode() for this ? Why you try for unnecessary while loop.

$query = $conn->prepare('SELECT id, name, salary from afreen'); 

Then try the json_encode() to get the Data in Json format.

Sources - http://php.net/json_encode

Answer 2

<?php header("Access-Control-Allow-Origin: *"); header('Content-type: application/json'); header('Content-Type: text/html; charset=utf-8'); $dns = "mysql:host=localhost;dbname=afreen"; $user = "root"; $pass = ""; try { $conn = new PDO($dns, $user, $pass); if (!$conn) { echo "Not connected"; } $query = $conn->prepare('SELECT id, name, salary from afreen'); $query->execute(); $registros= []; while ($result = $query->fetch()) { array_push($registros,array( 'id' =>$result["id"], 'title' =>$result["name"], 'salary' =>$result["salary"] )); $output = json_encode(array("product"=>$registros)); print_r($output); } catch (Exception $e) { echo "Erro: " . $e->getMessage(); } ?>