php - 使用 PHP 格式化 JSON

Question

我需要一些帮助来正确格式化我的 JSON。为每个事件创建父对象，然后向它们添加子对象。对于下面的示例数据，它将是一个具有两个子项的“Test”父事件和另一个具有三个子项的“Test2”父事件。我已经用我得到的格式和我需要的格式链接了两个 jsonblob。任何帮助，将不胜感激。

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

这就是我得到的:https://jsonblob.com/5550c921e4b002ae4e370469

这是我需要的:https://jsonblob.com/5550c942e4b002ae4e370471

编辑 -这是我的工作脚本最终的样子:

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

Answer 1

您没有显示您运行的一个/多个查询，但对于这样一个简单的任务，我认为一个查询就足够了。外层 do/while 在 sqlsrv_next_result() 上循环不需要。当您在一次调用 sqlsrv_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'];
    // 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