如何使用这个查询创建这个多维数组(最终转换为JSON)

Question

我已经做了我的研究。但是两天后我就快要发疯了。我真的希望避免向MYSQL发送多个查询。我知道解决方案是迭代并在新数组中插入值。但老实说，我不知道从哪里开始。我想创建的这个多维数组超出了我的能力范围。谢谢!

我需要转换以下MYSQL结果：

    categories  subcategories   items
    ----------  -------------   ------
    ART       painter        picasso
    ART       painter        VanGogh
    ART       sculpture      Rodin
    ART       sculpture      Serra
    MUSIC     Rock           Lennon
    MUSIC     Rock           Mercury
    MUSIC     Classic        Mozart
    THEATRE   Drama          Shakespeare
    THEATRE   Drama          Moliere

添加到此PHP数组中：

 $Data = array (
    name=>'ARTISTS', 
    children=>array(
        array('name'=>'ART', 
                children=>array(
        array('name'=>'Painting', children=>array(array('name'=>'picasso'),array('name'=>'Van Gogh'))),
        array('name'=>'Sculpture', children=>array(array('name'=>'Rodin'),array('name'=>'Serra')))
        )),
        array('name'=>'MUSIC', 
                children=>array(
        array('name'=>'Rock', children=>array(array('name'=>'Lennon'),array('name'=>'Mercury'))),
        array('name'=>'Classic', children=>array(array('name'=>'Mozart')))
        )),
        array('name'=>'THEATRE', 
                children=>array(
        array('name'=>'Drama', children=>array(array('name'=>'Shakespeare'),array('name'=>'Moliere')))
        ))
    )
);   

我所知道的唯一一件事是，这个PHP数组(手动编写)在通过json_encode传递后会生成一个完美的JSON。所以最后这部分不是问题所在。我把它放在这里只是为了解释这一切的进展。

$json = json_encode($Data);

生成：

{"name":"ARTISTS","children":[{"name":"ART","children":[{"name":"Painting","children":[{"name":"picasso"},{"name":"Van Gogh"}]},{"name":"Sculpture","children":[{"name":"Rodin"},{"name":"Serra"}]}]},{"name":"MUSIC","children":[{"name":"Rock","children":[{"name":"Lennon"},{"name":"Mercury"}]},{"name":"Classic","children":[{"name":"Mozart"}]}]},{"name":"THEATRE","children":[{"name":"Drama","children":[{"name":"Shakespeare"},{"name":"Moliere"}]}]}]}

Answer 1

我会放弃应用所有那些不必要的name和children标签的想法。当您获得数据库结果集时，只需遍历它并直接设置所有数组索引项。

$data = array();
while ($row = /*  your DB row fetch here */) {
    $data[$row['categories']][$row['subcategories']][] = $row[$items];
}

$json_data = json_encode($data);

Answer 2

正如Mike Brant所说，您应该使用类别的名称作为索引。但是如果你仍然需要一个解决方案来给出你给出的结果，下面是一些代码：

$formatted = array() ;

foreach($data as $part){
    if (!array_key_exists($part['categories'], $formatted)){
        $formatted[$part['categories']] = array(
            "name" => $part['categories'],
            "children" => array()
        );
    }

    $sub = &$formatted[$part['categories']]['children'] ;

    if (!array_key_exists($part['subcategories'], $sub)){
        $sub["name"] = $part['subcategories'] ;
        $sub["children"] = array() ;
    }

    $items = &$sub['children'] ;

    $items[$part['items']] = array("name"=>$part['items']) ;
}

print_r($formatted);