使用php和mysql在Json脚本中生成嵌套的相同结构表

<?php
// 建立数据库连接
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}
?>

<?php
// 生成嵌套的相同结构表的JSON数据
function generateNestedTable() {
    global $conn;

    $data = array();

    // 查询数据库中的数据
    $sql = "SELECT * FROM your_table";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $nestedData = array(
                "id" => $row["id"],
                "name" => $row["name"],
                "children" => array()
            );

            // 查询子数据
            $sqlChildren = "SELECT * FROM your_table WHERE parent_id = " . $row["id"];
            $resultChildren = $conn->query($sqlChildren);

            if ($resultChildren->num_rows > 0) {
                while ($rowChildren = $resultChildren->fetch_assoc()) {
                    $nestedData["children"][] = array(
                        "id" => $rowChildren["id"],
                        "name" => $rowChildren["name"]
                    );
                }
            }

            $data[] = $nestedData;
        }
    }

    return json_encode($data);
}
?>

<?php
// 调用函数并输出生成的JSON数据
$jsonData = generateNestedTable();
echo $jsonData;
?>