CODEWITHSUNDEEP
phpsqljson
ntalekt
ntalekt

Reputation: 852

PHP + JSON Formatting

I issue the following query and the output in MS SQL2008 is below.

Query:

SELECT * FROM (
SELECT cast(convert(varchar(10), DATE_PULL) as DATE) as d, 
[OWNER] as label, 
sum(VM_COUNT) as value 
FROM [VCENTER_INFO_HIST] 
GROUP BY OWNER, 
DATE_PULL)sub PIVOT (MAX(value) FOR label IN (OWNER1,OWNER2,OWNER3))pvt

Output:

+---------------------------------------------------------------------+
|    D       |   OWNER1       |      OWNER2       |      OWNER3       |
+---------------------------------------------------------------------+
| 2014-07-07 |    443         |       456         |       487         |
+---------------------------------------------------------------------+ 
| 2014-07-09 |    1024        |       2056        |       2076        |
+---------------------------------------------------------------------+

I'm using the following PHP script to grab the data and encode to JSON.

    <?php 
    include("connect.php");

    /* Set up and execute the query. */
    $sql = "SELECT * FROM (SELECT cast(convert(varchar(10), DATE_PULL) as DATE) as d,  [OWNER]    as label, sum(VM_COUNT) as value FROM [VCENTER_INFO_HIST] GROUP BY OWNER,   DATE_PULL)sub   PIVOT (MAX(value) FOR label IN (OWNER1,OWNER2,OWNER3))pvt";
    $stmt = sqlsrv_query( $conn, $sql);

    /* Process results */
    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);
    /* Free statement and connection resources. */
    sqlsrv_free_stmt( $stmt);
    sqlsrv_close( $conn);
    ?>

The resulting JSON looks like this:

[{"d":{"date":"2014-07-07 00:00:00","timezone_type":3,"timezone":"Asia\/Brunei"},"OWNER1":443,"OWNER2":456,"OWNER3":487},{"d":{"date":"2014-07-09 00:00:00","timezone_type":3,"timezone":"Asia\/Brunei"},"OWNER1":1024,"OWNER2":2056,"OWNER3":2076}]

However, I need the JSON to look like this. I'm not sure where the timezone type and timezone are coming from:

[{"d":"2014-07-07","OWNER1":443,"OWNER2":456,"OWNER3":487},{"d":"2014-07-09","OWNER1":1024,"OWNER2":2056,"OWNER3":2076}]

Is it something wrong with my query or in my PHP?

Thanks!

Upvotes: 0

Views: 496

Answers (2)

ntalekt
ntalekt

Reputation: 852

Thanks for all your help, I was able to get this accomplished by not doing the convert in SQL and doing it in PHP.

Query:

SELECT * FROM (
SELECT DATE_PULL as d, 
[OWNER] as label, 
sum(VM_COUNT) as value 
FROM [VCENTER_INFO_HIST] 
GROUP BY OWNER, 
DATE_PULL)sub PIVOT (MAX(value) FOR label IN (OWNER1,OWNER2,OWNER3))pvt

Output:

+---------------------------------------------------------------------+
|    D       |   OWNER1       |      OWNER2       |      OWNER3       |
+---------------------------------------------------------------------+
| 07-07-2014 |    443         |       456         |       487         |
+---------------------------------------------------------------------+ 
| 07-09-2014 |    1024        |       2056        |       2076        |
+---------------------------------------------------------------------+

PHP:

<?php 
include("connect.php");

/* Set up and execute the query. */
$sql = "SELECT * FROM (SELECT DATE_PULL as d,  [OWNER] as label, sum(VM_COUNT) as value FROM [VCENTER_INFO_HIST] GROUP BY OWNER, DATE_PULL)sub PIVOT (MAX(value) FOR label IN (OWNER1,OWNER2,OWNER3))pvt";
$stmt = sqlsrv_query( $conn, $sql);

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

foreach($json as &$each) {
// reformat date column   
$each['d'] = date('Y-m-d', strtotime($each['d']));
}


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

Upvotes: 0

scrowler
scrowler

Reputation: 24406

You'd be best to assemble your output array during your loop, and set it to the specific format you want. But a quick loop over the results before you encode it will give you what you want:

foreach($json as &$each) {
    // reassign "d" key to just the date (formatted), discard the rest
    $each['d'] = date('Y-m-d', strtotime($each['d']['date']));
}

echo json_encode($json);

Output:

[{"d":"2014-07-07","OWNER1":443,"OWNER2":456,"OWNER3":487},{"d":"2014-07-09","OWNER1":1024,"OWNER2":2056,"OWNER3":2076}]

Upvotes: 2

Related Questions