skip to Main Content

I want to add a set of arrays into the existing array with the same key. I can make those data out from db. But I cannot put them together to work.

DB

option_unit

PHP

$stmt=$mysqli->prepare("select * from option_unit order by level asc");
$stmt->execute();
$res=$stmt->get_result();           
    while($row=$res->fetch_assoc()){
        $_parent=$row['parent'];            
                //child
                $_chl=$mysqli->prepare("select * from option_unit where parent=?");
                $_chl->bind_param('i',$_parent);
                $_chl->execute();
                $res1=$_chl->get_result();
                while($row1=$res1->fetch_assoc()){
                    $chld[]=array("head"=>$row['title'],"id"=>$row['id'],"contents"=>$row['title'],"children"=>"");
                }                   
                $_chl->close();                 
                
                $data[]=array("head"=>$row['title'],"id"=>$row['id'],"contents"=>$row['title'],"children"=>$chld);
        
            }//while

$stmt->close();
echo json_encode($data,JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT);

JSON (The result is duplicated hundred of lines)

JSON result

But what I expected to outcome is:

[
    {
        "head": "CEO",
        "id": 24,
        "contents": "CEO",
        "children": [
            {
                "head": "Accountant",
                "id": 2,
                "contents": "CEO",
                "children": [{ "head": "Manager", "id": "25", "contents": "Mr.Bill" }]
            },{
                "head": "HR",
                "id": 9,
                "contents": "CEO",
                "children": ""
            }
    }
    ]

I think there must be something wrong to the adding array. But I don’t know what is it. If I know this one. Another tier of children would be easier.

2

Answers


  1. You never declare $chld nor are you resetting it.

    $stmt=$mysqli->prepare("select * from option_unit order by level asc");
    $stmt->execute();
    $res = $stmt->get_result();
    
    while ($row=$res->fetch_assoc()) {
        $_parent = $row['parent'];
        //child
        $_chl = $mysqli->prepare("select * from option_unit where parent=?");
        $_chl->bind_param('i', $_parent);
        $_chl->execute();
        $res1 = $_chl->get_result();
        $chld = [];
        while ($row1 = $res1->fetch_assoc()) {
            $chld[]=[
                "head" => $row1['title'],
                "id" => $row1['id'],
                "contents" => $row1['title'],
                "children" => ""];
        }
        $_chl->close();
    
        $data[]=[
            "head" => $row['title'],
            "id" => $row['id'],
            "contents" => $row['title'],
            "children" => $chld];
    }//while
    
    $stmt->close();
    echo json_encode($data, JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT);
    
    Login or Signup to reply.
  2. You can create recursive function this way so it can manage all the levels that you are expecting.

    function get_data($mysqli, $parent=0)
    {
        if($parent === 0) {
            $stmt=$mysqli->prepare("select * from option_unit where parent=0 order by level asc");
        } else {
            $stmt = $mysqli->prepare("select * from option_unit where parent=?");
            $stmt->bind_param('i',$_parent);
        }
    
        $stmt->execute();
        $data = [];
        $res=$stmt->get_result();           
        while($row=$res->fetch_assoc()){
            $_parent=$row['id'];
            $child = get_data($mysqli, $_parent); 
            $data[]=array("head"=>$row['title'],"id"=>$row['id'],"contents"=>$row['title'],"children"=>$child);
        }
        $stmt->close();
        return $data;
    }
    
    $final_data = get_data($mysqli);
    echo json_encode($final_data, JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT);
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search