JSON Formatting from SQL via PHP

Question

I'm trying to create a datatable that groups items based on a column name. In order to populate the table correctly I need to format the JSON with parent and child relationships but am having troubles. I'll be grabbing the data from MSSQL via PHP.

+---------------+-------+--------------+------------+------------+--------+
| ACTIVITY_NAME | GROUP |  START_DATE  |  END_DATE  | COMPLETED  | TOTAL  |
+---------------+-------+--------------+------------+------------+--------+
|          Test |     1 |  04/30/2015  |  05/01/2015|        10  |    15  |
|          Test |     2 |  04/30/2015  |  05/01/2015|        20  |    25  |
|         Test2 |     1 |   05/2/2015  |  05/03/2015|        30  |    35  |
|         Test2 |     2 |   05/2/2015  |  05/03/2015|        40  |    45  |
|         Test2 |     3 |   05/2/2015  |  05/03/2015|        50  |    55  |
+---------------+-------+--------------+------------+------------+--------+

I need the JSON formatted like this with the parent objects being under the "data" and the children being under the "children". I'm having a hard time figuring out if I need to do two seperate queries, one for the rollup data in the parent and the second for the child rows. Any help would be appriecated.

Edit to add PHP:

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

if( $conn === false ) {
   echo "Could not connect.\n";
   die( print_r( sqlsrv_errors(), true));
}
/* Set up and execute the query. */
$sql = "<QUERY>";
$stmt = sqlsrv_query( $conn, $sql);

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

     }
} while ( sqlsrv_next_result($stmt) );
foreach ($json as $result) {
    $data[$result['ACTIVITY_NAME']]['children'] = $result;
}
echo json_encode($data);
?>

I need this format: JSON blob: https://jsonblob.com/554b958be4b05c281ae9707e

{
"data": [
    {
        "ACTIVITY_NAME": "Test",
        "children": [
            {
                "ACTIVITY_NAME": "Test",
                "MAINTENANCE_GROUP": "1",
                "START_DATE": "04/30/2015",
                "END_DATE": "05/01/2015",
                "COMPLETED": "10",
                "TOTAL": "15"
            },
            {
                "ACTIVITY_NAME": "Test",
                "MAINTENANCE_GROUP": "2",
                "START_DATE": "04/30/2015",
                "END_DATE": "05/01/2015",
                "COMPLETED": "20",
                "TOTAL": "25"
            }
        ]
    },
    {
        "ACTIVITY_NAME": "Test2",
        "children": [
            {
                "ACTIVITY_NAME": "Test2",
                "MAINTENANCE_GROUP": "1",
                "START_DATE": "05/2/2015",
                "END_DATE": "05/03/2015",
                "COMPLETED": "30",
                "TOTAL": "35"
            },
            {
                "ACTIVITY_NAME": "Test2",
                "MAINTENANCE_GROUP": "1",
                "START_DATE": "05/2/2015",
                "END_DATE": "05/03/2015",
                "COMPLETED": "40",
                "TOTAL": "45"
            },
            {
                "ACTIVITY_NAME": "Test2",
                "MAINTENANCE_GROUP": "1",
                "START_DATE": "05/2/2015",
                "END_DATE": "05/03/2015",
                "COMPLETED": "50",
                "TOTAL": "55"
            }
        ]
    }
]
}

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

I will recommend you to use two queries, then put the data in an array that matches your format and json_encode it to produce the required result.

You may want to take a look at SQL Server recursive query

edited May 23 '17 at 11:43

Community

1
1

answered May 07 '15 at 16:45

gotha

489
1
5
20

Nokrosis · Answer 2 · 2015-05-07T17:38:07.603

1

It seems that you are grouping by ACTIVITY_NAME, if that's the case and you don't want to make additional queries, you can first make an associative array:

foreach ($results as $result) {
    $data[$result['ACTIVITY_NAME']]['children'] = $result;
}

Then you could use that array to iterate over the children to calculate data like MAINTENANCE_GROUP, COMPLETED and TOTAL;

After you've done all this, you can then use array_values to get a non-associative array.

edited May 07 '15 at 17:38

answered May 07 '15 at 17:33

Nokrosis

11
4

Yes I am grouping by ACTIVITY_NAME. I'll try what you suggested. Thanks. – solar411 May 07 '15 at 18:29
It only creates one child per parent event when there are multiple children. – solar411 May 07 '15 at 20:24

score 0 · Accepted Answer · answered Jun 02 '15 at 19:18

Here is what the working script looks like:

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

if( $conn === false ) {
   echo "Could not connect.\n";
   die( print_r( sqlsrv_errors(), true));
}
/* Set up and execute the query. */
$sql = "<query> ";
$stmt = sqlsrv_query($conn, $sql);

// This is where the data will be organized.
// It's better to always initialize the array variables before putting data in them
$data = array();

// Get the rows one by one
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    // Extract the activity name; we want to group the rows by it
    $name = $row['ACTIVITY_NAME'];
    $group = '';
    $sdate = '';
    $edate = '';
    $completed = '';
    $total = '';
    $perc = '';

    // Check if this activity was encountered before
    if (! isset($data[$name])) {
        // No, this is the first time; we will make room for it, first
        $data[$name] = array(
            // Remember the name
            'ACTIVITY_NAME' => $name,
            'MAINTENANCE_GROUP' => $group,
            'START_DATE' => $sdate,
            'END_DATE' => $edate,
            'COMPLETED' => $completed,
            'TOTAL_CLUSTERS' => $total,
            'COMPLETE_PERC' => $perc,
            // No children yet
            'children' => array(),
        );
    }
    // Put the row into the list of children for this activity
    $data[$name]['children'][] = $row;
}

// Here, the entries in $data are indexed by the values they also have in                  'ACTIVITY_NAME'
// If you want them numerically indexed, all you have to do is:
$data = array_values($data);
echo json_encode(array('data' => $data));
//echo json_encode($data);
?>

JSON Formatting from SQL via PHP

3 Answers3