php - 从数据库问题查询嵌套关联数组

Question

您好，我正在尝试查询一个关联数组，其最终结果应类似于下面的 json 数组。但是，我希望数据部分和 id 名称部分为每个节点查询一次，并且如果节点有多个邻接，则邻接部分查询多次，如果条件是，如何多次查询此嵌套数组的一个部分在维护此结构的同时满足其余部分，但一个节点有多个邻接或没有邻接？

   var json =[{
        "adjacencies": [{
           "nodeTo": "graphnode9",
           "nodeFrom": "graphnode5",
           "data": {}
        }],
        "data": {
            "$color": "#C74243",
            "$type": "triangle",
        },
        "id": "graphnode5",
        "name": "graphnode5"
  }];

这是我的数据库结构

nodes                 Relationships                      
-----                 -------------
id int(11),           id int(11),
name varchar(35),     to int(11), //this is the destination node from the id relation 
color varchar(7),     data varchar(0) null
type varchar (12),    Foreign key (id) references nodes(id)
Primary key (id)       

engine = innodb    

这是我尝试获取关联数组，但它一次查询所有内容并复制整个结构。

function getjson(){  
    $db = adodbConnect();
    $query = "SELECT nodes.*, relationships.* FROM nodes inner JOIN relationships ON nodes.id = relationships.id";
    $result = $db -> Execute($query);

    while($row=$result->FetchRow()) {
        $id = (float)$row['id'];
        $name = $row['name'];
        $color1 = $row['color'];
        $type1 = $row['type'];
        $to = (float)$row['to'];

        $array = array(
            "adjacencies:" => array(
                "nodeTo" => "$to",
                "nodeFrom" => "$id",
                "data" => array()
            ),
            "data" => array(
               "$"."color" => $color1,
               "$"."type" => $type1
            ),
            "id".":" => $id,
            "name".":" => $name
        );

    }
    print"$array";
    json_encode($array);
}

Answer 1

您需要在循环外部创建数组并向其中添加项目

$array = array();
while($row=$result->FetchRow()) {
    $array[] = array(
        "adjacencies:" => array(
            "nodeTo" => (float)$row['to'],
            "nodeFrom" => (float)$row['id'],
            "data" => array()
        ),
        "data" => array(
           '$color' => $row['color'],
           '$type' => $row['type']
        ),
        "id" => (float)$row['id'],
        "name" => $row['name']
    );

}