Title: Return JSON after MS SQL Server query
Tags: php;sql;sql-server;sql-server-2008-r2;sqlsrv
Question: I'm trying to return the result of a MS SQL Server query as a JSON object.
The query is correct, I also can see the result with echo $row ['name'], but I don't get any result with echo json_encode ( $arr ). The page just remains empty.
This is my code:
$sql = "SELECT * FROM tab1";
$stmt = sqlsrv_query ( $conn, $sql );
if ($stmt === false) {
die ( print_r ( sqlsrv_errors (), true ) );
$arr = array ();
while ( $row = sqlsrv_fetch_array ( $stmt ) ) {
// echo $row ['name'] . "\n";  // <- this works
array_push ( $arr, $row );
echo json_encode ( $arr );
sqlsrv_free_stmt ( $stmt );
sqlsrv_close ( $conn );
header ( "Content-type: application/json; charset=utf-8" );
die ( json_encode ( $arr ) );
exit ();
Comment: you should move echo json_encode ( $arr ); after header(...) and remove the line die(...) not usefull.

Comment: Can you log array output with `error_log(json_encode($arr));` just before `echo`?

Comment: I mean log file, not the page. You can log many things with `error_log()` method. We should understand if the problem is related with the array or the others like header definition.

Comment: @alalp: Whatever I try, if there is no error, like in this case, I always get an empty page.

Here is the accepted answer: OK, I finally managed to figure it out. The problem were the special characters in the table.
Simply adding characterSet into the connectionInfo helped:
$connectionInfo = array (
"Database" =&gt; "myDB",
"CharacterSet" =&gt; "UTF-8"

Here is another answer: Header must be sent to ouput.  Check installed extension (need php5-json).
Enable error reporting:
error_reporting(E_ALL);
Comment for this answer: heeu it's not about the header, but your suggestion error_reporting still a good idea to debug the problem.

Here is another answer: Put the header before the json_encode.
header ( "Content-type: application/json;" );
echo json_encode ( $arr );
Returning JSON from a PHP Script
Error reporting on the receiving page example.  Inside a jQuery script for AJAX, I will include console error reporting like this:
.fail(function( jqxhr, textStatus, error ) {
console.log(arguments);
var err = textStatus + ", " + error;
console.log( "Request Failed: " + err );
});
To view that console in browser, I often use "Developer Tools" windows in Chrome, or something like it.  This has been invaluable for debugging elementary errors in communication. If you are in a situation where you are not returning what you are expecting, this type of reporting to console can help.  JSON is as finicky as everything else with Javascript.  It will often just break into silence.
Similarly, to show the successful data that comes back you can report through a common alert box:
success: function (data){
alert(data+" was received.");
},
var_dump that arr and run the page by itself; compare that result with the success and fails in the JSON.  Using those kinds of success and fail routines, you should see if and what you are getting back on the blank page.
One other change I noticed is that I usually end with just the echo json encode line; maybe end with that unless you establish some flow control over that last bit and put it in its own block.
Comment for this answer: What about that array push?  Tried pushing $row ['name']  instead of just row?  As it is, aren't you putting an array in an array?  Most JSON will be about parsing a one-level string of key/value pairs.
Comment for this answer: When I troubleshoot JSON, I often use another plain PHP program to just call the script.  When I see that the values I load into json commands are working independently, then I step up to JSON.
Comment for this answer: That's the best I've got for you.  Good luck.
Comment for this answer: Thank you, but it didn't change anything in my case.
Comment for this answer: Yes, but unfortunately, without any changes.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 4, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2016:00:07/15/16', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<php><sql><sql-server><sql-server-2008-r2><sqlsrv>', 'Title': 'Return JSON after MS SQL Server query', 'ViewCount': 1116, 'id': 38397497, 'user_AboutMe': '', 'user_AccountId': 957889.0, 'user_DisplayName': 'Evgenij Reznik', 'user_Reputation': 17236.0, 'user_Views': 1117.0, 'user_id': 982865.0}}