<?php
    include("sql.php");
    $count = 0;
    $parent = 0;
    $data_array = [];
    $duplicate = [];

    $connectionInfo = array("Database" => "$dbname", "UID" => "$username", "PWD" => "$password", "CharacterSet" => "UTF-8");
    $conn = sqlsrv_connect($hostname, $connectionInfo);
    if ($conn === false) {
        die(print_r(sqlsrv_errors(), true));
    }

    $sql = "SELECT [category_name],[project_id],[project_name] FROM [Construction_Category]
    FULL JOIN [Project_Table] ON [Project_Table].[category_id] = [Construction_Category].[category_id];";

    $fetchResult = sqlsrv_query($conn, $sql);
    while ($row = sqlsrv_fetch_array($fetchResult)) {
        //parent
        if(!in_array($row["category_name"], $duplicate)){
            array_push($duplicate, $row["category_name"]);
            $data_array[$count] = [ "id" => "json".$count, "parent" => "#", "text" => $row["category_name"] ];
            $parent = $count;
            $count++;
        }
        //child
        if($row["project_id"] != ""){
            $data_array[$count] = [ "id" => "json".$count, "parent" => "json".$parent, "text" => $row["project_id"]."-".$row["project_name"] ];
            $count++;
        }
    }

    echo json_encode($data_array,JSON_UNESCAPED_UNICODE);
?>