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();
}
?>

• John Conde: Code dumps do not make for good answers. You should explain how and why this solves their problem. I recommend reading, "How do I write a good answer?"
• khanum: Parse error: syntax error, unexpected 'catch' (T_CATCH) in C:\xampp\htdocs\appmarket-api\listado.php on line 25
• Tarang koradiya: try and catch remove and check output