从平面PHP SQL表创建JSON树

What would be the cleanest and most efficient way to create a JSON tree representing a table from a MySQL query involving multiple JOINS ?

从涉及多个JOINS的MySQL查询创建表示表的JSON树的最干净,最有效的方法是什么?

So far, the php array is created by this loop:

到目前为止,php数组是由这个循环创建的:

$rs = mysqli_query($connect, $query);
$arr = array();
while ($row = mysqli_fetch_array($rs, MYSQL_ASSOC)) {
    $arr[] = $row;
}

I could then do echo $arr[2]["sold"] and get "2 sodas"

然后我可以回复$ arr [2] [“卖出”]并得到“2苏打水”

1 个解决方案

#1

Well here is my own solution to my own question !... For the benefits of ALL !!! It is working but can anyone suggest a better faster approach ?

那么这是我自己的问题的解决方案!...为了所有的好处!它正在运作,但任何人都可以提出更快的方法吗?

    $rs = mysqli_query($connect, $query);
$arr = array();
while ($row = mysqli_fetch_array($rs, MYSQL_ASSOC)) {

    $arr[] = $row;
}
$dateLevel = 0;
$employeeLevel = 0;
$soldLevel = 0;
$tree = array();
$count = count ($arr);
for ($i = 0; $i < $count; $i++){

    $A = $tree[$dateLevel-1];
    if ($A["text"] != $arr[$i]["date"]){
        $tree[$dateLevel]= array("text" => $arr[$i]["date"],  "expanded" => true, items => array());
        $dateLevel++;
        $employeeLevel = 0;
        $soldLevel = 0;
    }

    $A = $tree[$dateLevel-1];
    $B = $A["items"];
    $C = $B[$employeeLevel-1];
    if ($C["text"]  != $arr[$i]["employee"]){
        $tree[$dateLevel-1]["items"][$employeeLevel] = array ("text" => $arr[$i]["employee"],  "expanded" => true, items => array());
        $employeeLevel++;
        $soldLevel = 0;
    }

    $A = $tree[$dateLevel-1];
    $B = $A["items"];
    $C = $B[$employeeLevel-1];
    $D = $A["items"];
    if ($D["text"]  != $arr[$i]["sold"]){
        $tree[$dateLevel-1]["items"][$employeeLevel-1]["items"][$soldLevel] = array ("text" => $arr[$i]["sold"],  "expanded" => true, items => array());
        $soldLevel++;
    }
}

echo json_encode($tree);