What would be the cleanest and most efficient way to create a JSON tree representing a table from a MySQL query involving multiple JOINS ?
So far, the php array is created by this loop:
$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"
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);
$queryResult['date']['employee']['sold'], which would mean it's already in a nested format. Doessoldhave anemployeeId? and likewise doesemployeehave adateId? If that's the case I'd assume that is how you are performing the JOINs in the first place. Anyways, if the data from the query is flat, there needs to be some way for us to map each object to its parent.