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
– Mimouni, Commented Jul 15, 2016 at 14:11
Can you log array output with error_log(json_encode($arr)); just before echo? — alpakyol
– alpakyol, Commented Jul 15, 2016 at 15:01
@alalp: Whatever I try, if there is no error, like in this case, I always get an empty page. — Evgenij Reznik
– Evgenij Reznik, Commented Jul 15, 2016 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
– alpakyol, Commented Jul 18, 2016 at 10:42

S. Denis · Accepted Answer · 2016-07-15 14:09:40Z

1

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

error_reporting(E_ALL);

edited Jul 15, 2016 at 14:09

answered Jul 15, 2016 at 14:04

S. Denis

1593 silver badges12 bronze badges

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

1 Comment

Mimouni Over a year ago

heeu it's not about the header, but your suggestion error_reporting still a good idea to debug the problem.

Community · Accepted Answer · 2017-05-23 11:58:56Z

0

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.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Jul 15, 2016 at 13:38

gladiola

1335 bronze badges

5 Comments

Evgenij Reznik Over a year ago

Thank you, but it didn't change anything in my case.

gladiola Over a year ago

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.

Evgenij Reznik Over a year ago

Yes, but unfortunately, without any changes.

gladiola Over a year ago

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 Over a year ago

That's the best I've got for you. Good luck.

Evgenij Reznik · Accepted Answer · 2016-07-18 14:40:12Z

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, 2016 at 14:40

Evgenij Reznik

18.7k42 gold badges116 silver badges194 bronze badges

Collectives™ on Stack Overflow

Return JSON after MS SQL Server query

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related