如何用这个查询创建这个多维数组(最终变成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

Into This PHP Array:

 $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一旦你通过json_encode传递它。所以最后这部分不是问题。我把它放在这里只是为了解释这一切的走向。

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