PHP + JSON Formatting

Question

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!

im used to mysql so this may be wrong, but with mysql, you only need one loop with the fetch_array() in the while statement. — JStephen
– JStephen, Commented Jul 9, 2014 at 23:01
I think the do{while} loop is unnecessary, also id initialize the $json variable as an empty array to begin with. But I would say the query is not giving you what you want as well because this "timezone_type":3,"timezone":"Asia\/Brunei", which i see you already know. — ArtisticPhoenix
– ArtisticPhoenix, Commented Jul 9, 2014 at 23:36

scrowler · Accepted Answer · 2014-07-09 23:28:00Z

2

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}]

answered Jul 9, 2014 at 23:28

scrowler

24.4k10 gold badges67 silver badges99 bronze badges

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

7 Comments

ntalekt Over a year ago

The foreach loop should go after the $json[] = $row;?

ntalekt Over a year ago

Negative, not working. Just shows up blank and nothing in the $json variable.

scrowler Over a year ago

Have you included the & to pass the variable by reference?

ntalekt Over a year ago

Yes, I copied and pasted directly for your previous post.

scrowler Over a year ago

Try opening my example and copying and pasting that code (minus the $json assignment) and putting that into your script. Once you've got that working, work backwards removing it piece by piece until it's mostly your code again...

|

ntalekt · Accepted Answer · 2014-07-17 19:22:12Z

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

Collectives™ on Stack Overflow

PHP + JSON Formatting

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related