php - 转换为 JSON 数组，其中包含 3 个表中的数据

Question

您好，我是这个主题的新手，我真的需要真正了解这个主题的您的帮助。

下图显示了我的数据库设计和查询结果。

以下代码是我迄今为止尝试过的。

    <?php

include_once './DbConnect.php';

    $db = new DbConnect();

    $response = array();
    $response["content_info"] = array();

    $query = "SELECT Aid, AName, Bid, BName, Cid, CName, Content FROM A, B, C WHERE fk_Aid = Aid AND fk_Bid = Bid";
    $result = mysqli_query($db->getConnection(),$query);


    while($row = mysqli_fetch_array($result)){
        $tmp = array();
        $tmp["Aid"] = $row["Aid"];
        $tmp["AName"] = $row["AName"];
        $tmp["Bid"] = $row["Bid"];
        $tmp["BName"] = $row["BName"];
        $tmp["Cid"] = $row["Cid"];
        $tmp["CName"] = $row["CName"];
        $tmp["Content"] = $row["Content"];

        // push info to final json array
        array_push($response["content_info"], $tmp);

    }

    header('Content-Type: application/json');

    echo json_encode($response);
?>

但它返回以下 JSON 对象:

{
  "content_info": [
    {
      "Aid": "A1",
      "AName": "AName1",
      "Bid": "B1",
      "BName": "BName1",
      "Cid": "C1",
      "CName": "CName1",
      "Content": "aaaaaaa"
    },
    {
      "Aid": "A1",
      "AName": "AName1",
      "Bid": "B1",
      "BName": "BName1",
      "Cid": "C2",
      "CName": "CName2",
      "Content": "abdsdsfdsf"
    },
    {
      "Aid": "A1",
      "AName": "AName1",
      "Bid": "B2",
      "BName": "BName2",
      "Cid": "C3",
      "CName": "CName3",
      "Content": "dfefeeefeee"
    },
    {
      "Aid": "A1",
      "AName": "AName1",
      "Bid": "B2",
      "BName": "BName2",
      "Cid": "C4",
      "CName": "CName4",
      "Content": "fdsfdfdsf"
    },
    {
      "Aid": "A2",
      "AName": "AName2",
      "Bid": "B3",
      "BName": "BName3",
      "Cid": "C5",
      "CName": "CName5",
      "Content": "fsdfsfsfddf"
    }
  ]
}

但这不是我期望的 JSON 对象。以下结构是我想要的

{
  "content_info": [
    {
      "Aid": "A1",
      "AName": "AName1",
      "B": [
        {
          "Bid": "B1",
          "BName": "BName1",
          "C": [
            {
              "Cid": "C1",
              "CName": "CName1",
              "Content": "aaaaaaa"
            },
            {
              "Cid": "C2",
              "CName": "CName2",
              "Content": "abdsdsfdsf"
            }
          ]
        },
        {
          "Bid": "B2",
          "BName": "BName2",
          "C": [
            {
              "Cid": "C3",
              "CName": "CName3",
              "Content": "dfefeeefeee"
            },
            {
              "Cid": "C4",
              "CName": "CName4",
              "Content": "fdsfdfdsf"
            }
          ]
        }
      ]
    },
    {
      "Aid": "A2",
      "AName": "AName2",
      "B": [
        {
          "Bid": "B3",
          "BName": "BName3",
          "C": [
            {
              "Cid": "C5",
              "CName": "CName5",
              "Content": "fsdfsfsfddf"
            }
          ]
        }
      ]

如果有人知道，请告诉我我需要编辑什么。感谢所有为此尝试的人。

Answer 1

while($row = mysqli_fetch_array($result)){
        $tmp = array();
        $tmp["Aid"] = $row["Aid"];
        $tmp["AName"] = $row["AName"];
        $tmp["B"] = array();

        $query1 = "select * from B where fk_Aid= ".$row["Aid"];
        $result1 = mysqli_query($db->getConnection(),$query1);

        if($result1){
            while($row1 = mysqli_fetch_array($result1)){
                $tmp1 = array();
                $tmp1["Bid"] = $row1["Bid"];
                $tmp1["BName"] = $row1["BName"];

                $tmp1["C"] = array();

                $query2 = "select * from C where fk_Bid = ".$row1["Bid"];
                $result2 = mysqli_query($db->getConnection(),$query2);

                if($result2){
                    while($row2 = mysqli_fetch_array($result2)){
                        $tmp2 = array();
                        $tmp2["Cid"] = $row2["Cid"];
                        $tmp2["CName"] = $row2["CName"];
                        $tmp2["Content"] = $row2["Content"];

                        array_push($tmp1["C"], $tmp2);
                    }
                }

                array_push($tmp["B"], $tmp1);
        }
        }    
        array_push($response["content_info"], $tmp);    
    }