JSON from MS SQL Server with PHP Example

Someone much smarter than me, pointed out that my previous solution would hit a size limit (max cell width). This stored proc returns the JSON stream as a single table cell (albeit a big one). So their alternative solution is to do the JSON conversion on the webserver with PHP’s json_encode().

It works nicely.

EDIT: Don’t forget the UTF-8 encoding or you’ll experience random NULLs in your JSON.


<?php
/* Set Connection Credentials */
$serverName="(local)";
$uid = "";
$pwd = "";
$connectionInfo = array( "UID"=>$uid,
                         "PWD"=>$pwd,
                         "Database"=>"TEST",
                         "CharacterSet"=>"UTF-8");

/* Connect using SQL Server Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn === false ) {
     echo "Unable to connect.</br>";
     die( print_r( sqlsrv_errors(), true));
}

/* TSQL Query */
$tsql = "SELECT TOP 100 * FROM AtxJSON";
$stmt = sqlsrv_query( $conn, $tsql);

if( $stmt === false ) {
     echo "Error in executing query.</br>";
     die( print_r( sqlsrv_errors(), true));
}

/* Process results */
$json = array();

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

/* Run the tabular results through json_encode() */
/* And ensure numbers don't get cast to trings */
echo json_encode($json,<code> JSON_NUMERIC_CHECK</code>);
/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);

?>

Advertisements

5 thoughts on “JSON from MS SQL Server with PHP Example

  1. I got a problem at “$conn = sqlsrv_connect( $serverName, $connectionInfo);”.

    Error:
    Fatal error: Uncaught Error: Call to undefined function sqlsrv_connect().

  2. Why is there a syntax error on this line?
    echo json_encode($json, JSON_NUMERIC_CHECK);
    it states that: syntax error, unexpected ‘<' in C:\path on line ??

  3. how to create this format data
    {
    “contacts”: [
    {
    “id”: “c200”,
    “name”: “Ravi Tamada”,
    “email”: “ravi@gmail.com”,
    “address”: “xx-xx-xxxx,x – street, x – country”,
    “gender” : “male”,
    “phone”: {
    “mobile”: “+91 0000000000”,
    “home”: “00 000000”,
    “office”: “00 000000”
    }
    },
    {
    “id”: “c201”,
    “name”: “Johnny Depp”,
    “email”: “johnny_depp@gmail.com”,
    “address”: “xx-xx-xxxx,x – street, x – country”,
    “gender” : “male”,
    “phone”: {
    “mobile”: “+91 0000000000”,
    “home”: “00 000000”,
    “office”: “00 000000”
    }
    },
    .
    .
    .
    .
    ]
    }
    “JSON from MS SQL Server with PHP Example”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s