Binary Tree Using PHP & MySQL - Page 3

[imza72]

thanks a lot for sharing this code. it is really really useful and helpful. i hope ATLI is still follows this thread, so i could post any questions here when it arise.

[imza72]

anybody knows how to print full name instead of ID in the codes given by ATLI?

please....it is really urgent...

[Atli]

anybody knows how to print full name instead of ID in the codes given by ATLI?

please....it is really urgent...

Try this instead of what was posted earlier:
(This assumes you have a `name` column in your tree table, with the name you want shown.)

/**
     * A recursive function that fetches a tree-structure from a database into an array.
     * @global <mysqli> $dbLink A open MySQLI connection.
     * @param <array> $parentArray Container array for the output data.
     * @param <number> $parentID The ID the current recursion uses as a root.
     */
    private function fetchTree(&$parentArray, $parentID=null)
    {
        global $dbLink;

        // Create the query
        if($parentID == null) {
            $parentID = 0;
        }
        $sql = "SELECT `id`, `name` FROM `{$this->tblName}` WHERE `parentID`= ". intval($parentID);

        // Execute the query and go through the results.
        $result = $dbLink->query($sql);
        if($result)
        {
            while($row = $result->fetch_assoc())
            {
                // Create a child array for the current ID
                $currentID = $row['id'];
                $parentArray[$currentID] = array(
                    'name' => $row['name'],
                    'children' => array(),
                );

                // Print all children of the current ID
                $this->fetchTree($parentArray[$currentID]['children'], $currentID);
            }
            $result->close();
        }
        else {
            die("Failed to execute query! ($level / $parentID)");
        }
    }

    /**
     * Builds a HTML <div> hierarchy from the tree-view data.
     * Each parent is encased in a <div> with all their child nodes, and each
     * of the children are also encased in a <div> with their children.
     * @param <array> $data The tree-view data from the fetchTree method.
     * @param <string> $output The <div> hierachy.
     */
    private function buildHtml($data, &$output)
    {
        // Add the DIV hierarchy.
        foreach($data as $_id => $_branch)
        {
            $output .= "<div id=\"Tree{$GLOBALS['TreeView_DivID']}_{$_id}\"><p><span>{$_branch['name']} ({$_id})</span></p>";
            $this->buildHtml($_branch['children'], $output);
            $output .= "</div>";
        }
    }

// Add the borders, and create a margin for the lines so they are not
        // drawn "into" the numbers.
        if(borders.top) {
            overlay.css('border-top', this.BORDER_STYLE);
            over_size.height -= this.LINE_MARGIN;
        }
        if(borders.bottom) {
            overlay.css('border-bottom', this.BORDER_STYLE);
            //over_position.top += this.LINE_MARGIN;
            over_size.height -= this.LINE_MARGIN;
        }
        if(borders.left) {
            overlay.css('border-left', this.BORDER_STYLE);
            over_size.width -= ($(elem1).children("span").eq(0).width() / 2);
        }
        if(borders.right) {
            overlay.css('border-right', this.BORDER_STYLE);
            over_size.width -= ($(elem1).children("span").eq(0).width() / 2);
            over_position.left += ($(elem1).children("span").eq(0).width() / 2);
        }

It's not perfect, and long names will probably cause layout issues, but you should be able to fix that in your CSS or by modifying the JavaScript further.

[imza72]

Firstly, thanks for your reply. I already try your new code but received an error messages as below:

Warning: Invalid argument supplied for foreach() in C:\Program Files (x86)\AppServ\www\cnw\rangkaian\class.TreeView.php on line 176

Fyi, I still maintain the $depth variable. The codes as below (red color caused the error):

/**
     * A recursive function that fetches a tree-structure from a database into an array.
     * @global <mysqli> $dbLink A open MySQLI connection.
     * @param <array> $parentArray The pass-by-reference array to contain the results.
     * @param <number> $parentID The ID the current recursion uses as a root.
     * @param <number> $depth The current depth of the recursion.
     */
    private function fetchTree(&$parentArray, $parentID=null, $depth=0)
    {
        global $dbLink;
    
        // Create the query
        if($parentID == null) {
            $parentID = 0;
        }
        $sql = "SELECT `id`,`fname` FROM `{$this->tblName}` WHERE `parentID`= ". intval($parentID); 
    
        // Execute the query and go through the results.
        $result = $dbLink->query($sql);
        if($result)
        {
            while($row = $result->fetch_assoc())
            {
                // Add the ID to the levels array, at the current depth.
                $this->levels[$depth][] = $row['id'];
    
                // Create a child array for the current ID
                //$currentID = $row['id'];
                //$parentArray[$currentID] = array();

                // Create a child array for the current ID
                $currentID = $row['id'];
                //$parentArray[$currentID] = array();
                #$parentArray[$currentID] = array('name' => $row['fname'],'children' => array(),);

                // Print all children of the current ID
                //$this->fetchTree($parentArray[$currentID], $currentID, $depth+1);
                $this->fetchTree($parentArray[$currentID]['children'], $currentID, $depth+1);
            }
            $result->close();
        }
        else {
            die("Failed to execute query! ($level / $parentID)");
        }
    }
    
    /**
     * Builds a HTML <div> hierarchy from the tree-view data.
     * Each parent is encased in a <div> with all their child nodes, and each
     * of the children are also encased in a <div> with their children.
     * @param <array> $data The tree-view data from the fetchTree method.
     * @param <string> $output The <div> hierachy.
     */
    private function buildHtml($data, &$output)
    {
        // Add the DIV hierarchy.
        foreach($data as $_id => $_branch)
        {
            $output .= "<div id=\"Tree{$GLOBALS['TreeView_DivID']}_{$_id}\"><p><span><font size='-3'>{$_branch['name']} ({$_id})</font></span></p>";
            $this->buildHtml($_branch['children'], $output);
            $output .= "</div>";
        }
    }

[imza72]

Firstly, thanks for your reply. I already tried your new code and it's working perfectly. Thousands thanks. You are my saviour.

[imza72]

Hi,
I want to print the tree from any nodes. I already create one form to ask user to key-in their starting node. How am I going to pass the variable to class.TreeView.php?
At the moment, parentID is always null at the beginning of the fetchTree function.

Thanks in advance.

[imza72]

Hi,
Anyone knows how to do it? Please....

[imza72]

pleazzzzzzzzzzzzzzzz.....urgent...

[Elm6r]

Love this forums, lot of good info.

[kuink]

Hi all, thanks for ATLI code its works very well. but there is a question about my project, i'm going to create a binary tree with only 2 nodes, LEFT and Right. how can i do that with your code?

thanks in advanced

[sunithau]

Thanku so much Atli for posting this code.I am very happy with the code posted by you.

now iam able to display tree horizontally.

Is it possible to display tree vertically?

I have attached the screenshot for reference.Exactly i need the tree output in this way.

can you please help me to create the tree as shown in screenshot.

Ok. So I've been messing around, creating a JavaScript object that draws lines between the IDs in my tree-view code. Might help you.

This is basically the same code I posted before, with minor modifications to the Index and PHP file, and an added JavaScript file.
Note; I use jQuery for the JavaScript, loaded from the Google APIs.

I've tested this in all major browsers.

index.php
Uses the PHP class to generate the tree-structure, and includes and executes the JavaScript object that draws the lines between the tree elements.

<!DOCTYPE html>
<html>
    <head>
        <title>Tree-view Test</title>
        <meta http-equiv="Content-Type" content="text/html; charset=utf8">
        <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
        <script type="text/javascript" src="TreeView.js"></script>
        <script type="text/javascript">
            // Have the TreeView.drawAll method executed as soon as the DOM is ready.
            $(document).ready(function(){ 
                TreeView.drawAll();
            });
        </script>
    </head>
    <body>
        <?php
        // Fetch the TreeView class from the other file.
        include("class.TreeView.php");

        // Open a database connection
        // TODO: Replace the info here with your real info.
        $dbLink = new mysqli("localhost", "usr", "pwd", "dbName");

        // Create an instance of the TreeView class.
        $treeView = new TreeView($dbLink);

        // Print the tree view
        // TODO: Insert your real table name here.
        $treeView->printTree('dbTable');

        $dbLink->close();
        ?>
    </body>
</html>

class.TreeView.php
Uses the database to generate the tree-structure.

<?php
/**
 * Handles creating and/or printing a Tree-Like HTML output, complete with
 * all necessary CSS styles.
 *
 * Assumes a MySQL database table structure like so:
 *  CREATE TABLE `name` (
 *    `id` int(11) NOT NULL AUTO_INCREMENT,
 *    `parentID` int(11) DEFAULT NULL,
 *    PRIMARY KEY (`id`)
 *  );
 *
 * Public methods:
 *   createTree - Returns the HTML tree-view.
 *   printTree  - Prints the HTML tree-view.
 *
 * Private methods
 *   fetchTree  - Reads the complete tree structure into an array.
 *   buildHtml  - Builds the HTML div hierarchy based.
 */
class TreeView
{
    private $bgColor = ""; //"background-color: rgba(0, 100, 0, 0.10); ";

    private $dbLink;
    private $tblName;

    /**
     * Default constructor
     * @param mysqli $dbLink A open MySQL (mysqli) connection.
     * @throws Exception
     */
    public function __construct(mysqli $dbLink)
    {
        if($dbLink != null && $dbLink->connect_errno == 0)
        {
            $this->dbLink = $dbLink;

            // This number is added the the container DIV ID, so that we can
            // tell the DIVs a part if there are more than one view created.
            if(!isset($GLOBALS['TreeView_DivID'])) {
                $GLOBALS['TreeView_DivID'] = 0;
            }
        }
        else
        {
            throw new Exception("The mysqli object provided is invalid.");
        }
    }

    /**
     * Creates a descending tree-like view of the tree-structure in the given
     * database table and returns it as a string.
     * @param <type> $tblName The name of the database table to use.
     * @return <string> The string output.
     * @throws Exception
     */
    public function createTree($tblName)
    {
        if(!isset($tblName) || empty($tblName))
        {
            throw new Exception("Failed to create the tree. Table or database information is invalid");
        }
        else
        {
            // Set up variables
            $this->tblName = $tblName;
            $treeData = array();
            $output = "";

            // Create the output
            $this->fetchTree($treeData);

            // Set up the CSS styles, and create the container DIV.
            $divID = "TreeView_ContainerDiv_" . $GLOBALS['TreeView_DivID'];
            $output = <<<HTML
<style type="text/css">
    div#{$divID}     { margin: 0; padding: 0; text-align: center; }
    div#{$divID} div { margin: 0; padding: 0 10px; float: left; {$this->bgColor}}
    div#{$divID} p   { margin: 0; padding: 0; margin-bottom: 10px; }
</style>
<div id="{$divID}">
HTML;

            // Add the DIV hierachy.
            $this->buildHtml($treeData, $output);

            // Add the JavaScript call to start drawing the lines
            $rootID = array_keys($treeData);
            $rootID = $rootID[0];
            $output .= <<<HTML

<script type="text/javascript">
TreeView.addTree('Tree{$GLOBALS['TreeView_DivID']}_{$rootID}');
</script>
HTML;

            // Increment the DIV ID number
            $GLOBALS['TreeView_DivID']++;

            return $output;
        }
    }

    /**
     * Prints a descending tree-like view of the tree-structure in the given
     * database table.
     * @param <type> $tblName The name of the database table to use.
     * @throws Exception
     */
    public function printTree($tblName)
    {
        echo $this->createTree($tblName);
    }

    /**
     * A recursive function that fetches a tree-structure from a database into an array.
     * @global <mysqli> $dbLink A open MySQLI connection.
     * @param <number> $parentID The ID the current recursion uses as a root.
     */
    private function fetchTree(&$parentArray, $parentID=null)
    {
        global $dbLink;

        // Create the query
        if($parentID == null) {
            $parentID = 0;
        }
        $sql = "SELECT `id` FROM `{$this->tblName}` WHERE `parentID`= ". intval($parentID);

        // Execute the query and go through the results.
        $result = $dbLink->query($sql);
        if($result)
        {
            while($row = $result->fetch_assoc())
            {
                // Create a child array for the current ID
                $currentID = $row['id'];
                $parentArray[$currentID] = array();

                // Print all children of the current ID
                $this->fetchTree($parentArray[$currentID], $currentID);
            }
            $result->close();
        }
        else {
            die("Failed to execute query! ($level / $parentID)");
        }
    }

    /**
     * Builds a HTML <div> hierarchy from the tree-view data.
     * Each parent is encased in a <div> with all their child nodes, and each
     * of the children are also encased in a <div> with their children.
     * @param <array> $data The tree-view data from the fetchTree method.
     * @param <string> $output The <div> hierachy.
     */
    private function buildHtml($data, &$output)
    {
        // Add the DIV hierarchy.
        foreach($data as $_id => $_children)
        {
            $output .= "<div id=\"Tree{$GLOBALS['TreeView_DivID']}_{$_id}\"><p>{$_id}</p>";
            $this->buildHtml($_children, $output);
            $output .= "</div>";
        }
    }
}
?>

TreeView.js
Draws the lines between the elements in the PHP generated tree-structure.

/**
 * Handles drawing lines between the tree structures generated by PHP.
 */
var TreeView =
{
    /** Constants (sort of) **/
    LINE_MARGIN : 10, // px
    BORDER_STYLE : "solid 1px #555555",

    /** A list of root elements to draw when the window is loaded. */
    trees : [],

    /**
     * Adds a tree to the list of trees to be drawn.
     * @param rootID The ID of the root element of the tree.
     */
    addTree : function(rootID)
    {
        this.trees.push(rootID);
    },

    /**
     * Loops through all the trees and executes the drawing function for each of them.
     */
    drawAll : function()
    {
        for(var x = 0; x < this.trees.length; x++)
        {
            var root = $("#" + this.trees[x]);
            if(root.length > 0)
            {
                this.drawTree(root);
            }
        }
    },

    /**
     * Recursively draws all lines between all root-child elements in the given tree.
     * @param root The root element of the tree to be drawn.
     */
    drawTree : function(root)
    {
        root = $(root);
        if(root.length > 0)
        {
            var children = root.children('div');
            for(var i = 0; i < children.length; i++)
            {
                this.drawLine(root, children[i]);
                this.drawTree(children[i]);
            }
        }
    },

    /**
     * Draws a line between the two passed elements.
     * Uses an absolutely positioned <div> element with the borders as the lines.
     * @param elem1 The first element
     * @param elem2 The second element
     */
    drawLine : function(elem1, elem2)
    {
        // Use the <p> element as the base. Otherwise the height() call on the
        // <div> will return the entire hight of the tree, including the children.
        elem1 = $(elem1).find("p").eq(0);
        elem2 = $(elem2).find("p").eq(0);

        var e1_pos = $(elem1).position();
        var e2_pos = $(elem2).position();
        var borders = { top:true, left:true, right:false, bottom:false };

        // Move the position to the center of the element
        e1_pos.left += ($(elem1).width() / 2);
        e1_pos.top += ($(elem1).height() / 2);
        e2_pos.left += ($(elem2).width() / 2);
        e2_pos.top += ($(elem2).height() / 2);

        // Position if they are horizontally aligned.
        if(e1_pos.left == e2_pos.left)
        {
            borders.top = false;
            if(e1_pos.top < e2_pos.top)
            {
                e1_pos.top += ($(elem1).height() / 2);
                e2_pos.top -= ($(elem2).height() / 2);
            }
            else
            {
                e1_pos.top -= ($(elem1).height() / 2);
                e2_pos.top += ($(elem2).height() / 2);
            }
        }

        // Position if they are verticaly aligned.
        else if(e1_pos.top == e2_pos.top)
        {
            borders.left = false;
            e1_pos.top += ($(elem1).height() / 2);
            e2_pos.top += ($(elem2).height() / 2);
            if(e1_pos.left < e2_pos.left)
            {
                e1_pos.left += $(elem1).width();
            }
            else
            {
                e2_pos.top += $(elem2).height();
            }
        }

        // Position if the elements are not aligned.
        else
        {
            if(e1_pos.left < e2_pos.left)
            {
                borders.right = true;
                borders.left = false;
            }

            if(e1_pos.top > e2_pos.top)
            {
                borders.bottom = true;
                borders.top = false;
            }
        }

        // Calculate the overlay position and size
        var over_position = {
            left:(e1_pos.left < e2_pos.left ? e1_pos.left : e2_pos.left),
            top:(e1_pos.top < e2_pos.top ? e1_pos.top : e2_pos.top)
        };
        var over_size = {
            width:Math.abs(e1_pos.left - e2_pos.left),
            height:Math.abs(e1_pos.top - e2_pos.top)
        }

        // Create the overlay div
        var raw_overlay = document.createElement('div');
        var overlay = $(raw_overlay);

        // Add the borders, and create a margin for the lines so they are not
        // drawn "into" the numbers.
        if(borders.top) {
            overlay.css('border-top', this.BORDER_STYLE);
            over_size.height -= this.LINE_MARGIN;
        }
        if(borders.bottom) {
            overlay.css('border-bottom', this.BORDER_STYLE);
            over_position.top += this.LINE_MARGIN;
            over_size.height -= this.LINE_MARGIN;
        }
        if(borders.left) {
            overlay.css('border-left', this.BORDER_STYLE);
            over_size.width -= this.LINE_MARGIN;
        }
        if(borders.right) {
            overlay.css('border-right', this.BORDER_STYLE);
            over_position.left += this.LINE_MARGIN;
            over_size.width -= this.LINE_MARGIN;
        }

        overlay.css('position', 'absolute');
        overlay.css('top', over_position.top);
        overlay.css('left', over_position.left);
        overlay.css('width', over_size.width);
        overlay.css('height', over_size.height);

        document.body.appendChild(overlay.get(0));
    }
}

[Staggan]

Hello, I have been trying to understand this thread but am struggling.

I need something similar to this for creating pedigree's for my dogs... but I seem unable to find a simple explanation or tutorial anywhere.

My database has the following setup:

registeredName
father
mother

ALL names are unique....

How can I create a tree for any registeredName for X generations and print it out in a similar format to what has been shown?

Any advice and help would be greatly appreciated...

[sjpatil86]

Hey atli i use your code for my mlm binary tree project.it work nice.but i want to count left and right node of tree.then how i count the nodes?

[rotten69]

This thread is Fantastic and a lot of useful postings are here. Good Work, guys.

[nishuyadav87]

Hey guis i want to show first 15 members of binary tree in my mlm project.any buddu know how to show it using php and mysql.plesae help mi...

[a_situ]

thank you for your code, i want to show username along with the id in treeview. would you pls help me.

[muhammadkamrananjum]

hi hru i can appriciate you your code is very good but give error for me
plz send me all the correct code of the tree plz send me urgently thanks

[Dharmendra_2]

HI Atli,

How can I get level count only upto 5

Thanks

[Dharmendra_2]

Hi Atili,

And the level calculate only if root has both leg prenent, else consider as incomplete level.

Thanks

[Suresh_13]

nice post. but can we implement position through left or right. bcz i noticed that when i add id 16 in left of under parent id 15 then 16 id display below 15 not left of 15 so can i implement through left or right position?? or any design issue? plzz ans..

[Suresh_13]

any answer? plz

[Manabendra]

How to show user define parrent id tree