Return JSON after MS SQL Server query

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 ();

you should move echo json_encode ( $arr ); after header(...) and remove the line die(...) not usefull. — Mimouni, Jul 15 '16 at 14:11
Can you log array output with `error_log(json_encode($arr));` just before `echo`? — alpakyol, Jul 15 '16 at 15:01
@alalp: Whatever I try, if there is no error, like in this case, I always get an empty page. — Evgenij Reznik, Jul 15 '16 at 15:24
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. — alpakyol, Jul 18 '16 at 10:42

S. Denis · Answer 1 · 2016-07-15T14:09:40.693

1

Header must be sent to ouput. Check installed extension (need php5-json). Enable error reporting:

error_reporting(E_ALL);

edited Jul 15 '16 at 14:09

answered Jul 15 '16 at 14:04

S. Denis

149
3
11

heeu it's not about the header, but your suggestion error_reporting still a good idea to debug the problem. – Mimouni Jul 15 '16 at 14:10

score 0 · Answer 2 · edited May 23 '17 at 11:58

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.

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. — gladiola, Jul 15 '16 at 13:43
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. — gladiola, Jul 15 '16 at 13:47

score 0 · Accepted Answer · answered Jul 18 '16 at 14:40

0

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" => "myDB",
        "CharacterSet" => "UTF-8"
);

answered Jul 18 '16 at 14:40

Evgenij Reznik

17,916
39
104
181

Return JSON after MS SQL Server query

3 Answers3