sql server data to json using php

Question

My json code doesn't display anything, I have already tried many codes but nothing has helped.

include('connect.php');
$sql = "SELECT *  FROM items";  
$stmt = sqlsrv_query( $conn, $sql);

if( $stmt === false)
{
   echo "Error in query preparation/execution.\n";  
   die( print_r( sqlsrv_errors(), true));  
}

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))  //this loop is working
{    
  echo $row['item_id'].", ".$row['item_name'].", ".$row['Barcode']."<br>"; 
}

$json = array();
do {
   while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
     $json[] = $row;
   }
} while ( sqlsrv_next_result($stmt) );

echo json_encode($json);  //empty?!
sqlsrv_free_stmt( $stmt);

@aynber having formatted the code better I don't think that's the issue — ADyson
– ADyson, Commented May 25, 2018 at 14:03
@OscarZarrus having formatted the code better I don't think that's the issue — ADyson
– ADyson, Commented May 25, 2018 at 14:05
@ADyson Good catch. Formatting makes things so much easier to read. — aynber
– aynber, Commented May 25, 2018 at 14:06

ADyson · Accepted Answer · 2018-05-25 14:05:38Z

1

There are numerous likely issues with this:

1) Have you checked your query actually returns rows?

2) You're looping your data twice (two while( $row = sqlsrv_fetch_array... loops) which is not useful or efficient.

3) the do...while ( sqlsrv_next_result($stmt) ); clause should be unnecessary as well, as fetch_array will know when it's got to the end of the data, and you've only got one resultset, so you don't need to move between them

4) you're echoing raw data as well as JSON, so if you make an ajax call to this script it'll fail because the response will partly contain non-JSON data

I think this will be sufficient to get you some sensible data:

include('connect.php');
$sql = "SELECT *  FROM items";  
$stmt = sqlsrv_query( $conn, $sql);

if( $stmt === false)
{
   echo "Error in query preparation/execution.\n";  
   die( print_r( sqlsrv_errors(), true));  
}

$json = array();

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
{    
     $json[] = $row;
}

echo json_encode($json);

edited May 25, 2018 at 14:05

answered May 25, 2018 at 13:57

ADyson

62.9k16 gold badges86 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Oscar Zarrus Over a year ago

Right, I was about to answer the same thing. There's a strange nested loop and array resets each statement +1

Emily Over a year ago

my query is working, I tried your code but it doesn't show anything too.

Emily Over a year ago

json is still empty

ADyson Over a year ago

@EnasAK just for testing then, after the end of the loop, write var_dump($json); Does it show some results in your output? If it does, then you definitely have some data. In that the only logical possibility is that the json_encode operation failed. You can easily test this because you can check whether it returns false and see what the error was: write $encodedJSON = json_encode($json); if ($encodedJSON === false) echo json_last_error_msg(); else echo $encodedJSON;. See php.net/manual/en/function.json-last-error-msg.php (and json_last_error() will get you the error ID)

Oscar Zarrus Over a year ago

this is an Enigma. Can you var_dump the $row variable each interation? what are the results? We must call an exorcist. If $row is an array and echo $row['item_id'] prints something, the $json var must be populated.

|

Oscar Zarrus · Accepted Answer · 2018-05-25 14:14:53Z

If THIS works:

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))  //this loop is working
{    
  echo $row['item_id'].", ".$row['item_name'].", ".$row['Barcode']."<br>"; 
}

the rest must work too.

As ADyson says:

if( $stmt === false)
{
   echo "Error in query preparation/execution.\n";  
   die( print_r( sqlsrv_errors(), true));  
}

$json = array();

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
{    
     $json[] = $row;
}

echo json_encode($json);

for double check add your echo in this code, like this:

if( $stmt === false)
{
   echo "Error in query preparation/execution.\n";  
   die( print_r( sqlsrv_errors(), true));  
}

$json = array();

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
{    
     echo $row['item_id'].", ".$row['item_name'].", ".$row['Barcode']."<br>";
     $json[] = $row;
}

echo json_encode($json);

If this code works, accept the ADyson answer

paule mandengue · Accepted Answer · 2018-05-25 14:24:15Z

0

here is a way to solve the issue .Hoping your query is well written.

$dataFinal= array();//final variable that will contain total array for json   
for($k=0;$k<count(variable_that_contents_the_resutl_array_query);$k++){

$ligne = array("item_id"=> $row['item_id'],
"item_name"=>$row['item_name'],"Barcode"=> $row['Barcode']); 
array_push($dataFinal, $ligne);//add line in array datafinal         

     }//end for loop
    $result = array($dataFinal);
    $response = new Response(json_encode($dataFinal));
    $response->headers->set('Content-Type', 'application/json');
    return $response;

answered May 25, 2018 at 14:24

paule mandengue

645 bronze badges

Collectives™ on Stack Overflow

sql server data to json using php

3 Answers 3

11 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related