Output a JSON in PHP generated on SQL SERVER with JSON AUTO

user_5594363 0
Viewed 660

I am trying to get the output of a JSON formatted on SQL SERVER through command FOR JSON AUTO I need to execute the query in PHP on SQL SERVER and then output it as a legit JSON. How should I proceed? I normally use the code below to generate the JSON, but what if I need to get a JSON ?

$key= $_GET['key'];
$date=$_GET['date'];
$brand=$_GET['brand'];
    if ($key=="...")
{

    $serverName = "XXX,YYYY"; // \\MSSQLSERVER";
    $connectionOptions = [
    "Database" => "db",
    "UID" => "user",
    "PWD" => 'xxxx'
    ];

    $conn = sqlsrv_connect($serverName, $connectionOptions);
    if ($conn === false) {
        die(formatErrors(sqlsrv_errors()));
    }
            $tsql = "select * from admin_all.Datafeed FOR JSON AUTO;";


    // Executes the query
    $stmt = sqlsrv_query($conn, $tsql);
            // Error handling
    if ($stmt === false) {
        die(formatErrors(sqlsrv_errors()));
    }
    

    $array = array();
    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {



        $array[]=$row;
    }
    echo json_encode(array("data"=>array_values($array)));

    sqlsrv_free_stmt($stmt);
    sqlsrv_close($conn);
}
sqlsrv sql-server php json
2 Answers

The reason for the unexpected result is that json_encode() call is not needed (but it's always an option). FOR JSON AUTO returns a valid JSON, so you only need to echo the generated JSON. Note, that if you want to add a single, top-level element to the JSON output of the FOR JSON clause, you need to use the ROOT option.

Example, based on your attempt:

<?php
...
$json = '';
if ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_BOTH)) {
   $json = $row[0];
}
echo '{"data":'.$json.'}';
...
?>

Example with json_encode():

<?php
...

$sql = "SELECT id FROM (VALUES (1), (2)) v(id)";
$stmt = sqlsrv_query($conn, $sql);
if($stmt == false){
    die( print_r( sqlsrv_errors(), true) );
}

$json = array();
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    $json[] = $row;
}
echo json_encode(array("data" => array_values($json)));

...
?>

Example with FOR JSON AUTO and the ROOT option:

<?php
...

$sql = "SELECT id FROM (VALUES (1), (2)) v(id) FOR JSON AUTO, ROOT('data')";
$stmt = sqlsrv_query($conn, $sql);
if($stmt == false){
    die( print_r( sqlsrv_errors(), true) );
}

$json = '';
if ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) {
    $json = $row[0];
}
echo $json;

...
?>

Generated JSON:

{"data":[{"id":1},{"id":2}]}
user_65780800

PHP example;

 // This file is to be called by AJAX
 <?php
 require_once('db.php');
 $select = mysqli_query($con, "select * from table where index='something'");
 $row[]=mysqli_fetch_array($select);
 echo json_encode($row);
 ?>

JS example (fetches the PHP above) *note: this uses jQuery 3.4.1

 $(document).ready(function() {
 $.ajax({

 url:"myserverfile.php",
 method:"POST",
 dataType:"json",

 success:function(response) { // Doesn't have to be called response, can be anything
 var a=(response[0]['indexone']); // A name of an index from the sql row you are retrieving
 var b=(response[0]['indextwo']);  // A name of an index from the sql row you are retrieving
 var c=(response[0]['indexthree']);  // A name of an index from the sql row you are retrieving

 // Check to see values
 console.log(a);
 console.log(b);
 console.log(c);
 }

 });
 });

Tested on a working server with live database.

user_102730810
Related