Format JSON with PHP -

i need helping formatting json correctly. create parent objects each of activities , add children them. example data below 1 parent activity 'test' 2 children , parent 'test2' 3 children. i've linked in 2 jsonblobs format i'm getting , format need. 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 , execute query. */ $sql = "<query>"; $stmt = sqlsrv_query( $conn, $sql);  {      while ($row = sqlsrv_fetch_array($stmt, sqlsrv_fetch_assoc)) {      $json[] = $row;       } } while ( sqlsrv_next_result($stmt) );  foreach ($json $result) {     $data[data][][$result['activity_name']]['children'] = $result; } echo json_encode($data); ?> 

this i'm getting: https://jsonblob.com/5550c921e4b002ae4e370469

this need: https://jsonblob.com/5550c942e4b002ae4e370471

edit - here working script ended looking like:

<?php  include("connect.php");  if( $conn === false ) {    echo "could not connect.\n";    die( print_r( sqlsrv_errors(), true)); } /* set , execute query. */ $sql = "<query> "; $stmt = sqlsrv_query($conn, $sql);  // data organized. // it's better initialize array variables before putting data in them $data = array();  // rows 1 one while ($row = sqlsrv_fetch_array($stmt, sqlsrv_fetch_assoc)) {     // extract activity name; want group rows     $name = $row['activity_name'];     $group = '';     $sdate = '';     $edate = '';     $completed = '';     $total = '';     $perc = '';      // check if activity encountered before     if (! isset($data[$name])) {         // no, first time; make room it, first         $data[$name] = array(             // remember 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 row list of children activity     $data[$name]['children'][] = $row; }  // here, entries in $data indexed values have in                  'activity_name' // if want them numerically indexed, have is: $data = array_values($data); echo json_encode(array('data' => $data)); //echo json_encode($data); ?> 

you didn't show query/queries run such simple task think single query enough. outer do/while loop on sqlsrv_next_result() not needed. have use when send more 1 query (separated semicolons) in single call sqlsrv_query().

you not need run 2 times through result set. can organize data database.

all need check values database , create data structure need:

// ... $stmt = sqlsrv_query($conn, $sql);  // data organized. // it's better initialize array variables before putting data in them $data = array();  // rows 1 one while ($row = sqlsrv_fetch_array($stmt, sqlsrv_fetch_assoc)) {     // extract activity name; want group rows     $name = $row['activity_name'];     // check if activity encountered before     if (! isset($data[$name])) {         // no, first time; make room it, first         $data[$name] = array(             // remember name             'activity_name' => $name,             // no children yet             'children' => array(),         );     }     // put row list of children activity     $data[$name]['children'][] = $row; }  // here, entries in $data indexed values have in 'activity_name' // if want them numerically indexed, have is: $data = array_values($data);  // that's