PHP多维父子数组 - 某些元素被覆盖

Question

I am having real trouble generating a parent-child tree out of two database tables.

It's meant to reference folders and files inside them.

I am almost there, the code below generates the tree (sourced from here), but any files that are assigned to a category that contains sub-categories- they don't show..

How can I make them show?

Here is what is currently happening

Here is what I am wanting to happen

I believe the issue is with function Generate_Tree_Of_Categories($Tree_Data).

function Generate_Tree_Of_Categories(array $elements, $parentId = "NONE")
{
    $branch = array();

    foreach ($elements as $element) {
        if ($element['child_of'] == $parentId) {
            $children = Generate_Tree_Of_Categories($elements, $element['id']);
            if ($children) {
                $element['children'] = $children;

            }
            $branch[] = $element;
        } else {
          $element = null;
        }
    }

    return $branch;
}

Here are my DB tables:

**Categories table:**
Reference       Title                   Type        Child_Of    Status
CAT202480   Software                    CATEGORY    NONE        ACTIVE
CAT893984   Product Manuals             CATEGORY    NONE        ACTIVE
CAT384594   Manufacturing               CATEGORY    NONE        ACTIVE
CAT394858   Manufacturing Manuals       CATEGORY    CAT384594   ACTIVE
CAT394811   Videos                      CATEGORY    NONE        ACTIVE
CAT111338   Product Videos              CATEGORY    CAT394811   ACTIVE
CAT339844   Commissioning Software      CATEGORY    CAT202480   ACTIVE
CAT339845   Release Versions            CATEGORY    CAT339844   ACTIVE
CAT339846   Beta Versions               CATEGORY    CAT339844   ACTIVE

**Files Table:**
Reference       Type        Title                       Category_Ref    Format  Status
FILE001393804   SOFTWARE    Beta software v0.9.3            CAT339846   ZIP     AVAILABLE
FILE001984843   DOCUMENT    Product A Installation Manual   CAT893984   PDF     AVAILABLE
FILE009039742   DOCUMENT    Product A Commissioning Guide   CAT893984   PDF     AVAILABLE
FILE683579248   DOCUMENT    Product A User Guide            CAT893984   PDF     AVAILABLE
FILE001393805   SOFTWARE    Product A software Release 1.9  CAT339845   ZIP     AVAILABLE
FILE001393803   SOFTWARE    Product Z program               CAT339844   ZIP     AVAILABLE

Here is an SQL dump

Here's my code for converting the above data into JSTree compatible arrays, which I later json_encode.

function Load_Downloads_TreeView()
{
    require '../../global/session_manager.php';
    require_once '../../permissions/permissions.php';

    // Determine the permissions for the current user
    $User_Permissions = Get_Permissions_SpecificUser($LoggedInUserReference, $LoggedInFlag);


    $Response = Retrieve_All_Download_Categories_SpecificStatus("ACTIVE", $LoggedInFlag);
    // $Response = Retrieve_All_Download_Categories($LoggedInFlag);
    if ($Response['Decision'] == TRUE) {
        $Download_Categories = $Response['Value'];
    } else {
        echo $Response['Notification'];
        exit();
    }


    // $Response          = Retrieve_Specific_Download_Category("CAT339845", $LoggedInFlag);
    // $Specific_Category = $Response['Value'];

    $Tree_Data = array();
    foreach ($Download_Categories as $Current_Category) {


              // This code returns an array of files assigned to the $Current_Category reference
              $Response = Retrieve_Files_In_Download_Category($Current_Category['Reference'], $LoggedInFlag);
              $Files    = $Response['Value'];
              $Child_Nodes = NULL;
              if (count($Files) > 0) {
                 $Child_Nodes = Return_Files($Files);
              }

            //$Child_Nodes would be filled with the array of files (array of arrays).

            // $Child_Nodes = NULL; //Until I figure out how to nest this information in a multidimensional array with categories

            $Response = Generate_Folder_Node($Current_Category['Reference'],$Current_Category['Name'],$Current_Category['Child_Of'], $Child_Nodes);

            $Tree_Data[] = $Response;

    }

    $Generated_Tree = Generate_Tree_Of_Categories($Tree_Data);

    echo json_encode($Generated_Tree);


}



function Generate_Folder_Node($Element_ID,$Element_Name,$Child_Of, $Child_Nodes) {
  if (!defined('id')) define('id', 'id');
  if (!defined('text')) define('text', 'text');
  if (!defined('type')) define('type', 'type');
  if (!defined('child_of')) define('child_of', 'child_of');
  if (!defined('children')) define('children', 'children');
  if (!defined('state')) define('state', 'state');
  if (!defined('opened')) define('opened', 'opened');
  $Node_Open_Array = array(opened=>"false");

  $Generated_Node = array(id=> $Element_ID, text=>$Element_Name, type=>"folder", child_of=>$Child_Of, children=>$Child_Nodes, state=>$Node_Open_Array);

return $Generated_Node;

}



function Return_Files($Child_Array)
{
  $All_Nodes = array();

    foreach ($Child_Array as $Current_Child_Element) {
        $Element_ID   = $Current_Child_Element['Reference'];
        $Element_Name = $Current_Child_Element['Name'];


        if ($Current_Child_Element['Status'] == "AVAILABLE") {
            $DisabledState = "false";
        } else if ($Current_Child_Element['Status'] == "UNAVAILABLE") {
            $DisabledState = "true";
        }

        if ($Current_Child_Element['File_Type'] == "PDF") {
            $Icon = "fa fa-file-pdf-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "ZIP") {
            $Icon = "fa fa-file-zip-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "JPG" || $Current_Child_Element['File_Type'] == "PNG") {
            $Icon = "fa fa-file-picture-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "XLS" || $Current_Child_Element['File_Type'] == "XLSX") {
            $Icon = "fa fa-file-excel-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "DOC" || $Current_Child_Element['File_Type'] == "DOCX") {
            $Icon = "fa fa-file-word-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "PPT" || $Current_Child_Element['File_Type'] == "PPTX") {
            $Icon = "fa fa-file-powerpoint-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "EXE") {
            $Icon = "fa fa-desktop fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "AVI" || $Current_Child_Element['File_Type'] == "MP4") {
            $Icon = "fa fa-file-video-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "MP3" || $Current_Child_Element['File_Type'] == "WAV") {
            $Icon = "fa fa-file-sound-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "TXT") {
            $Icon = "fa fa-file-text-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "HEX" || $Current_Child_Element['File_Type'] == "BIN") {
            $Icon = "fa fa-file-code-o fa-lg text-inverse";
        } else {
            $Icon = "fa fa-file fa-lg text-inverse";
        }

      $Generated_Node = Generate_File_Node($Element_ID,$Element_Name,$Icon,$DisabledState);

      if (is_array($Generated_Node) == true) {
        if (!empty($Generated_Node)) {
          array_push($All_Nodes, $Generated_Node);

        }
      }


      // $All_Nodes[] = $Child_Node;
    }

    return $All_Nodes;

}

function Generate_File_Node($Element_ID,$Element_Name,$Icon,$DisabledState) {

if (!defined('id')) define('id', 'id');
if (!defined('text')) define('text', 'text');
if (!defined('type')) define('type', 'type');
if (!defined('icon')) define('icon', 'icon');
if (!defined('state')) define('state', 'state');
if (!defined('opened')) define('opened', 'opened');
$Node_Open_Array = array(opened=>$DisabledState);

$Generated_Node = array(id=> $Element_ID, text=>$Element_Name, type=>"file", icon=>$Icon, state=>$Node_Open_Array);


return $Generated_Node;

}

Answer 1

Here is my code. There is probably a better way. I actually removed one of your functions and incorporated it into the other function:

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8" /> 
        <title>
        </title>
        <meta name="generator" content="BBEdit 11.5" /> 
    </head>
    <body>
<?php
$link = mysqli_connect("127.0.0.1", "root", "root", "stackoverflow");

if (!$link) {
    echo "Error: Unable to connect to MySQL." . PHP_EOL;
    echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
    echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
    exit;
}
$sql="SELECT * from Downloads_Categories ORDER BY id";
$result=mysqli_query($link,$sql);
$Download_Categories = mysqli_fetch_all($result,MYSQLI_ASSOC);

$FolderTree = Generate_Tree_Of_Categories($Download_Categories);
usort($FolderTree, function ($item1, $item2) {
    if ($item1['id'] == $item2['id']) return 0;
    return $item1['id'] < $item2['id'] ? -1 : 1;
});

echo "<pre>" . json_encode($FolderTree, JSON_PRETTY_PRINT) . "</pre>";

mysqli_close($link);

function Generate_Tree_Of_Categories(array $elements, $parentId = "NONE")
{
    global $link;
    $branch = array();
    foreach ($elements as $element) {
        if ($element["Child_Of"] == $parentId) {
            $sql="SELECT * from Downloads_Files as DF WHERE '" . $element['Reference'] . "' = DF.Child_Of";
            $result=mysqli_query($link,$sql);
            $Files = mysqli_fetch_all($result,MYSQLI_ASSOC);
            $Child_Nodes = NULL;

            if (count($Files) > 0) {
                 $Child_Nodes = Return_Files($Files); 
            }
            $children = array_merge(Generate_Tree_Of_Categories($elements, $element['Reference']), Return_Files($Files));

            if ($children) {
                $element += ['children' => $children];
            }
            else {
                $element += ['children' => "NONE"];
            }

            $node = Generate_Folder_Node($element["id"],$element["Name"],$element["Child_Of"], $element["children"]);
            $branch[]=$node;
        }   
    }
    return $branch;
}

function Generate_Folder_Node($Element_ID,$Element_Name,$Child_Of, $Child_Nodes) {

    if (!defined('id')) define('id', 'id');
    if (!defined('text')) define('text', 'text');
    if (!defined('type')) define('type', 'type');
    if (!defined('Child_Of')) define('Child_Of', 'Child_Of');
    if (!defined('children')) define('children', 'children');
    if (!defined('state')) define('state', 'state');
    if (!defined('opened')) define('opened', 'opened');
    $Node_Open_Array = array(opened=>"false");
    $Generated_Node = array(id=> $Element_ID, text=>$Element_Name, type=>"folder", Child_Of=>$Child_Of, children=>$Child_Nodes, state=>$Node_Open_Array);
    return $Generated_Node;
}



function Return_Files($Child_Array) {

    $All_Nodes = array();

    foreach ($Child_Array as $Current_Child_Element) {
        $Element_ID   = $Current_Child_Element['Reference'];
        $Element_Name = $Current_Child_Element['Name'];

        if ($Current_Child_Element['Status'] == "AVAILABLE") {
            $DisabledState = "false";
        } else if ($Current_Child_Element['Status'] == "UNAVAILABLE") {
            $DisabledState = "true";
        }

        if ($Current_Child_Element['File_Type'] == "PDF") {
            $Icon = "fa fa-file-pdf-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "ZIP") {
            $Icon = "fa fa-file-zip-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "JPG" || $Current_Child_Element['File_Type'] == "PNG") {
            $Icon = "fa fa-file-picture-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "XLS" || $Current_Child_Element['File_Type'] == "XLSX") {
            $Icon = "fa fa-file-excel-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "DOC" || $Current_Child_Element['File_Type'] == "DOCX") {
            $Icon = "fa fa-file-word-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "PPT" || $Current_Child_Element['File_Type'] == "PPTX") {
            $Icon = "fa fa-file-powerpoint-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "EXE") {
            $Icon = "fa fa-desktop fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "AVI" || $Current_Child_Element['File_Type'] == "MP4") {
            $Icon = "fa fa-file-video-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "MP3" || $Current_Child_Element['File_Type'] == "WAV") {
            $Icon = "fa fa-file-sound-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "TXT") {
            $Icon = "fa fa-file-text-o fa-lg text-inverse";
        } else if ($Current_Child_Element['File_Type'] == "HEX" || $Current_Child_Element['File_Type'] == "BIN") {
            $Icon = "fa fa-file-code-o fa-lg text-inverse";
        } else {
            $Icon = "fa fa-file fa-lg text-inverse";
        }

      $Generated_Node = Generate_File_Node($Element_ID,$Element_Name,$Icon,$DisabledState);

      if (is_array($Generated_Node) == true) {
        if (!empty($Generated_Node)) {
          array_push($All_Nodes, $Generated_Node);
        }
      }
    }
    return $All_Nodes;
}

function Generate_File_Node($Element_ID,$Element_Name,$Icon,$DisabledState) {

    if (!defined('id')) define('id', 'id');
    if (!defined('text')) define('text', 'text');
    if (!defined('type')) define('type', 'type');
    if (!defined('icon')) define('icon', 'icon');
    if (!defined('state')) define('state', 'state');
    if (!defined('opened')) define('opened', 'opened');
    $Node_Open_Array = array(opened=>$DisabledState);

    $Generated_Node = array(id=> $Element_ID, text=>$Element_Name, type=>"file", icon=>$Icon, state=>$Node_Open_Array);

    return $Generated_Node;
}
?>
    </body>
</html>

Answer 2

It may be as simple as the Category_Ref for the Product Z program. You have it has CAT339844. Shouldn't it be CAT339846 ?

Answer 3

I don't know if this is what you are looking for, but I rewrote it so that it gives this JSON output. How are you translating that into a visual display of the structure. I don't see the code for that.

[
    {
        "id": "1",
        "text": "Software",
        "type": "folder",
        "Child_Of": "NONE",
        "children": [
            {
                "id": "7",
                "text": "Commissioning Software",
                "type": "folder",
                "Child_Of": "CAT202480",
                "children": [
                    {
                        "id": "8",
                        "text": "Release Versions",
                        "type": "folder",
                        "Child_Of": "CAT339844",
                        "children": [
                            {
                                "id": "FILE001393805",
                                "text": "Product A software Release 1.9",
                                "type": "file",
                                "icon": "fa fa-file-zip-o fa-lg text-inverse",
                                "state": {
                                    "opened": "false"
                                }
                            }
                        ],
                        "state": {
                            "opened": "false"
                        }
                    },
                    {
                        "id": "9",
                        "text": "Beta Versions",
                        "type": "folder",
                        "Child_Of": "CAT339844",
                        "children": [
                            {
                                "id": "FILE001393804",
                                "text": "Beta software v0.9.3",
                                "type": "file",
                                "icon": "fa fa-file-zip-o fa-lg text-inverse",
                                "state": {
                                    "opened": "false"
                                }
                            }
                        ],
                        "state": {
                            "opened": "false"
                        }
                    },
                    {
                        "id": "FILE001393803",
                        "text": "Product Z program",
                        "type": "file",
                        "icon": "fa fa-file-zip-o fa-lg text-inverse",
                        "state": {
                            "opened": "false"
                        }
                    }
                ],
                "state": {
                    "opened": "false"
                }
            }
        ],
        "state": {
            "opened": "false"
        }
    },
    {
        "id": "2",
        "text": "Product Manuals",
        "type": "folder",
        "Child_Of": "NONE",
        "children": [
            {
                "id": "FILE001984843",
                "text": "Product A Installation Manual",
                "type": "file",
                "icon": "fa fa-file-pdf-o fa-lg text-inverse",
                "state": {
                    "opened": "false"
                }
            },
            {
                "id": "FILE009039742",
                "text": "Product A Commissioning Guide",
                "type": "file",
                "icon": "fa fa-file-pdf-o fa-lg text-inverse",
                "state": {
                    "opened": "false"
                }
            },
            {
                "id": "FILE683579248",
                "text": "Product A User Guide",
                "type": "file",
                "icon": "fa fa-file-pdf-o fa-lg text-inverse",
                "state": {
                    "opened": "false"
                }
            }
        ],
        "state": {
            "opened": "false"
        }
    },
    {
        "id": "3",
        "text": "Manufacturing",
        "type": "folder",
        "Child_Of": "NONE",
        "children": [
            {
                "id": "4",
                "text": "Manufacturing Manuals",
                "type": "folder",
                "Child_Of": "CAT384594",
                "children": [
                    {
                        "id": "FILE2000000001",
                        "text": "Product A Manufacturing Manual",
                        "type": "file",
                        "icon": "fa fa-file-word-o fa-lg text-inverse",
                        "state": {
                            "opened": "false"
                        }
                    }
                ],
                "state": {
                    "opened": "false"
                }
            }
        ],
        "state": {
            "opened": "false"
        }
    },
    {
        "id": "5",
        "text": "Videos",
        "type": "folder",
        "Child_Of": "NONE",
        "children": [
            {
                "id": "6",
                "text": "Product Videos",
                "type": "folder",
                "Child_Of": "CAT394811",
                "children": "NONE",
                "state": {
                    "opened": "false"
                }
            }
        ],
        "state": {
            "opened": "false"
        }
    }
]

Answer 4

For anyone wishing to use this solution, below is a link to the JStree initialisation instructions and below that is my code that I use to populate the tree:

Initialisation: https://www.jstree.com/docs/config/

Note- be sure to check out the plugins and modify the initialisation code to include those. Thankfully the website template I purchased came with a custom theme for JSTree and all the relevant plugins.

function Generate_Downloads_TreeView() {
    var FunctionToRun = "Request_Download_Tree";
  var http = $.ajax({
    type: "POST",
    url: "controller.php",
    dataType: "json",
    data: {
      FunctionToRun: FunctionToRun
    }

  });
  http.done(function(data, textStatus, jQxhr) {

        $('#jstree-default').jstree({
        "core": {
                    'data': data,


        },
        "types": {
            "default": { "icon": "fa fa-folder text-warning fa-lg" },
            "file": { "icon": "fa fa-file text-warning fa-lg" }
        },
        "plugins": [ "dnd", "state", "types" ]
    });

  });

Here is the tree working: enter image description here

Thank you once again to @sscotti !