Format JSON with PHP

Question

I need some helping formatting my JSON correctly. Create parent objects for each of the activities and then add children to them. With the example data below it would be one parent activity for 'Test' with two children and another parent for 'Test2' with three children. I've linked in two jsonblobs with the format i'm getting and the format I need. Any help would be appreciated.

+---------------+-------+--------------+------------+------------+--------+
| 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  |
+---------------+-------+--------------+------------+------------+--------+

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[data][][$result['ACTIVITY_NAME']]['children'] = $result;
}
echo json_encode($data);
?>

This is what i'm getting: https://jsonblob.com/5550c921e4b002ae4e370469

This is what I need: https://jsonblob.com/5550c942e4b002ae4e370471

Edit - Here is what my working script ended up looking 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);
?>

axiac · Accepted Answer · 2015-05-14 16:57:08Z

5

+50

You didn't show the query/queries you run but for such a simple task I think a single query is enough. The outer do/while loop on sqlsrv_next_result() is not needed. You have to use it when you send more than one query (separated by semicolons) in a single call to sqlsrv_query().

You do not need to run two times through the result set. You can organize your data as soon as you get it from the database.

All you need is to check the values you get from the database and create the data structure as you need:

// ...
$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'];
    // 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,
            // 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);

// That's all

answered May 14, 2015 at 16:57

axiac

73.4k11 gold badges120 silver badges150 bronze badges

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

2 Comments

ntalekt Over a year ago

How do the get the object/array names like in the second blob?

ntalekt Over a year ago

Figured it out echo json_encode(array('data' => $data));

charmeleon · Accepted Answer · 2015-05-14 16:43:38Z

1

Change these 3 lines:

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

To this:

foreach ($json as $result) {
    $data['data'][] = array('ACTIVITY_NAME' => $result['ACTIVITY_NAME'], 'children' => $result);
}

answered May 14, 2015 at 16:43

charmeleon

2,7551 gold badge28 silver badges35 bronze badges

1 Comment

ntalekt Over a year ago

This added all children to all parents.

jpintor · Accepted Answer · 2015-05-14 16:48:49Z

1

You can save the children fields in an array

do {
 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    $children = array("MAINTENANCE_GROUP" => $row["MAINTENANCE_GROUP"], "START_DATE" => $row["START_DATE"], 
        "END_DATE" => $row["END_DATE"], "COMPLETED" => $row["COMPLETED"], "TOTAL" => $row["TOTAL"] );

    $json[] = array("MAINTENANCE_GROUP" => $row["MAINTENANCE_GROUP"], "children" => $children);

 }
} while ( sqlsrv_next_result($stmt) );

answered May 14, 2015 at 16:48

jpintor

4195 silver badges21 bronze badges

Comments

bob_1982 · Accepted Answer · 2015-05-14 17:04:12Z

1

i guess use below code

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'];
// 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,
        // No children yet
        'children' => array(),
    );
}
// Put the row into the list of children for this activity
$data[$name]['children'][] = $row;
}
 $json=json_encode($data);

answered May 14, 2015 at 17:04

bob_1982

7416 silver badges17 bronze badges

Collectives™ on Stack Overflow

Format JSON with PHP

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related