Geek Answers Handbook

JSON format with PHP

I need help formatting my JSON correctly. Create parent objects for each action, and then add children to them. In the examples below, this will be one parent action for “Test” with two children and another parent for “Test2” with three children. I contacted in two jsonblobs with the format I get and in the format I need. Any help would be greatly 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 get : https://jsonblob.com/5550c921e4b002ae4e370469

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

Edit - This is what ended up with my working script as follows:

<?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 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);
?>
+4
source share
4

/, , , . do/while sqlsrv_next_result() . , ( ) sqlsrv_query().

. , .

, , , , :

// ...
$stmt = sqlsrv_query($conn, $sql);

// This is where the data will be organized.
// It 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 all
+5

3 :

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

:

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

You can save child 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) );
+1
source

I suggest using 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);
+1
source

More articles:


All Articles