从SQL Server检索数据并将其转换为json格式？

Question

I am using PHP 5.6.0 and connected to my local SQL Server. I was able to retrieve the data, but it is in an array format. I would like to convert it into a json format.

What I get:

(     
    [date] => 2013-02-05 16:02:02.000000
    [timezone_type] => 3
    [timezone] => America/New_York
)

What I want:

(
    "date" : "2013-02-05 16:02:02.000000",
    "timezone_type" : "3",
    "timezone" : "America/New_York"
)

Here is my code:

$sql = "SELECT * FROM table";
$stmt = sqlsrv_query($conn, $sql);

$result = array(); 

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


echo json_encode($result);


sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);

My understanding is that json_encode should convert my data but it doesn't seem to work that way.

Thank you!

Answer 1

I guess there were two mistakes in your code. First one is

$result[] = print_r($row); 

You are executing a function and pushing values in array at same time. You should push value in array here. Like

$result[] = $row;

And Second one is, not printing the JSON varibale after encoding it. So your code would be

$sql = "SELECT * FROM table";
$stmt = sqlsrv_query($conn, $sql);

$result = array(); 

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


sqlsrv_free_stmt($stmt);
sqlsrv_close($conn); //Close the connnectiokn first

echo json_encode($result); //You will get the encoded array variable