Retrieving data from SQL Server and turning it into a json format?

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!

why are you using print_r($row)? Is that just a typo with some debugging? If not try it without the print_r — Styphon
– Styphon, Commented Sep 9, 2015 at 15:40
@Styphon I am using print_r because it's the only way I can actually see the code. without it, I get a blank screen. — KST Studio
– KST Studio, Commented Sep 9, 2015 at 15:46
Then you should use print_r($row); $result[] = $row;. What you currently have is invalid. — Styphon
– Styphon, Commented Sep 9, 2015 at 16:00

AleMelo · Accepted Answer · 2015-09-09 15:39:09Z

3

Your logic is quite unsual try it like this:

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

$result = array(); 

while ($row = mssql_fetch_array($stmt))
{
 array_push($result, $row);
} 

echo json_enconde($result);


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

answered Sep 9, 2015 at 15:39

AleMelo

1284 bronze badges

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

3 Comments

KST Studio Over a year ago

Thank for this code but I am using SQL Server so I can't use mssql_fetch_array()

AleMelo Over a year ago

Yes you can! mssql_fetch_array is not mysql_fetch_array as seen here php.net/manual/en/function.mssql-fetch-array.php

Antero Duarte Over a year ago

I can't suggest an edit, but for anyone copying and pasting this, there's a typo in the line that starts with echo. It should be json_encode not json_enconde

Vineet · Accepted Answer · 2015-09-09 15:57:42Z

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

answered Sep 9, 2015 at 15:57

Vineet

4,7153 gold badges25 silver badges42 bronze badges

2 Comments

KST Studio Over a year ago

Thank you for submitting @Vineet but this still didn't help for some reason. I got a blank screen.

Vineet Over a year ago

call die() or exit() at the end of the your script.

Jovan MSFT · Accepted Answer · 2015-09-22 08:49:23Z

1

Sql Server 2016 has FOR JSON clause that automatically transforms result set to JSON see Format Query Results as JSON with FOR JSON (SQL Server)

answered Sep 22, 2015 at 8:49

Jovan MSFT

14.8k4 gold badges44 silver badges59 bronze badges

Collectives™ on Stack Overflow

Retrieving data from SQL Server and turning it into a json format?

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related