php - 如何使用此查询创建此多维数组(最终变成 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);