Binary Tree Using PHP & MySQL

[hemgoyal_1990]

I m working on a MLM project in which i have to store member automatically in binary tree form like

1 is root
2 is child of 1
3 is child of 1

Then 4 is child of 2
5 is child of 2

6 is child of 3
7 is child of 3

then 8 is child of 4

and so on ......

I Want to Create a Downline in above Structure. Please Help me.

can any body help me

[network18]

to store the elements in the binary tree form is not much difficult.
Create table with fields 1)element 2)level 3)parent_id in it

[hemgoyal_1990]

okay...
but how i can create downline from this database structure?

[Atli]

Hey.

You could do this using a recursive function.

For example, using a table like this:

mysql> SELECT * FROM tree;
+----+----------+
| id | parentID |
+----+----------+
|  1 |     NULL |
|  2 |        1 |
|  3 |        1 |
|  4 |        1 |
|  5 |        2 |
|  6 |        3 |
|  7 |        2 |
|  8 |        5 |
|  9 |        4 |
+----+----------+

You could do:

<?php
header("content-type: text/plain");

/**
 * A recursive function that prints a tree-structure.
 * @global <mysqli> $dbLink A open MySQLI connection. 
 * @param <number> $level Used to determine how far from the root the current ID is.
 * @param <number> $parentID The ID the current recursion uses as a root.
 */
function printTree($level=0, $parentID=null)
{
    global $dbLink;

    // Create the query
    $sql = "SELECT id FROM tree WHERE ";
    if($parentID == null) {
        $sql .= "parentID IS NULL";
    }
    else {
        $sql .= "`parentID`=" . intval($parentID);
    }

    // Execute the query and go through the results.
    $result = $dbLink->query($sql);
    if($result)
    {
        while($row = $result->fetch_assoc())
        {
            // Print the current ID;
            $currentID = $row['id'];
            for($i = 0; $i < $level; $i++) {
                echo " ";
            }
            echo $currentID . PHP_EOL;

            // Print all children of the current ID
            printTree($level+1, $currentID);
        }
        $result->close();
    }
    else {
        die("Failed to execute query! ($level / $parentID)");
    }
}

// Open a MySQLI connection and execute the function.
$dbLink = new mysqli("host", "usr", "passwd", "dbName");
printTree();
$dbLink->close();
?>

Which would give you:

1
 2
  5
   8
  7
 3
  6
 4
  9

Hope that helps.

[hemgoyal_1990]

@above.
Thanx for Your Solution. Your Provide Solution is Most Helpful for me but i want to Create the Downline in Below Mention Structure:

Structure:
                                      ID(1)
                                         /\
                               ID(2)       ID(3)
                                 /\              /\
                          ID(4) ID(5) ID(6) ID(7)

Example:
                                         A
                                         /\
                                      B    C
                                     /\     /\
                                   D  E  F  G

Hope You are Understand.
Some Information:
Table Name is: admin_regdata
field: name, ID, Sponsor_ID, Address, Date_of_joining

Please Hep me.

[hemgoyal_1990]

anyone please help..
it's urgent..

[wenzlerpaul]

Hi,
I am new to this forum and just wanted to share my thoughts on your issue.

I was on the same boat as you are so I know how hard it is. Let me explain how you can structure this.

my structure consists of 3 important fields, membercode, uplinecode and position

to create the binary, you first have to have a head, so add one on the database, then from there, you use that first member id as your upline code. Now that you have your upline code, you can now add 2 members to serve as your binary, membercode1 position left, membercode2 position right, both with same upline code, you now created your first downline... you can continue doing this to the next level... I will try to illustrate this as much as I can:

on my database, it will be
membercode sponsorcode position
code1 headcode Left
code2 headcode Right

So now headcode already have left and right, to continue,
membercode sponsorcode position
code1 headcode Left
code2 headcode Right
newcode1 code1 Left
newcode2 code1 Right
thirdcode1 newcode1 Left
thirdcode2 newcode1 Right

based on the illustration above, headcode will have 3 levels down on binary already.

headcode
                     /      \
                  code1     code2
                /      \
       newcode1      newcode2
      /       \
thirdcode1   thirdcode2

The trick here is the upline code that is tagging on the membercode together with the position, since both sides are already filled, then you can simply try to validate and skip the member who already has left and right.

Hope it helps,
Paul

[hemgoyal_1990]

@above Thanx for Your Help.

I am Understand what u Want to Understanding me.
but Tell me The PHP Code that How Print the Above Illustration.
I am Understand that How to Store Binary Tree Data in Database.

any Help will be Appreciated.
Thank You.

[wenzlerpaul]

Hi,

You can try to approach this in different ways, however, you will need to see how you would layout your page too. Based on my experience, the layout you were looking at will grow wider so you will need to limit how much you can show per page.

I will try to give you 2 approaches I can think of that might work for you.

First, limit your display up to 15 IDs only, this will allow you to put as much details as you need per ID, please refer to the illustration taken from my genealogy system:

http://i34.tinypic.com/j9x63b.jpg

This method will allow your database to breathe from over-processing because of recursive calls.
What I did with this was just layout an html page and used a function call to get left and right till you fill all 15 slots. Here is the php code I use:

function GetDownline($member_id,$direction) 
    { 
$getdownlinesql = @mysql_fetch_assoc(@mysql_query('select memberid,placementid,position from `yourrelationaltable` where placementid="'.$member_id.'" and  position="'.$direction.'"'));
$getdownline = $getdownlinesql['memberid'];
return $getdownline; 
    }

then simply call it with:
$firstleft = GetDownline('headmemberidhere','Left'); //for first left
$firstright = GetDownline('headmemberidhere','Right'); //for first right
echo $firstleft;
echo $firstright;

Now for the next left or right

$secondleftofleft = GetDownline($firstleft,'Left'); //for second left of first left
$secondrightofleft = GetDownline($firstleft,'Right'); //for second right of first left

echo $secondleftofleft;
echo $secondrightofleft;

$secondleftofright = GetDownline($firstright,'Left'); for second left of first right
$secondrightofright = GetDownline($firstright,'Right'); for second right of first right

echo $secondleftofright;
echo $secondrightofright;

..And so on, now you can build your 15 tree line on a page, to give the effect of going downline, you can make all of the buttons clickable carrying their own member id's so it makes it the top of the tree, just to a GET or POST method to assign a new value for headmemberidhere that serves as your head of the tree.

Note:
This is not my exact code and is for illustration purposed only, but the code will work as is.

That is the simplest method to have a nice layout of the binary tree.

The next one is the full recursive method similar to Atli's post.

However, this approach is not recommended if you are on a shared server or have a large database like I have.

The full recursive method I did was a bit complicated to get the effect due to the nature of my database structure. I actually had an existing database to work on so I had difficulties adapting or creating the proper code since I had to adapt to the database instead of me creating the perfect code that will do a simple recursive structure.

Below is the method I used to make recursion work from top to bottom of the tree in full view.

I did three function calls to so that each set will call different codes at the same time in recursion, I am not sure how to do this easily too because of the existing database structure.

Here is an illustrative php code for you:

<?php
include("../includes/config.php"); 
//my database connection is set in my config, otherwise, just create your own db connect
$defaultmcode = 'yourdefaultidhere';
if($_GET['topmcode']){
$topmcode = trim($_GET['topmcode']);
}else{
$topmcode = $defaultmcode;
}
$topmcode = ltrim($topmcode);
$topmcode = rtrim($topmcode);
$topmcode = strtoupper($topmcode);
//my memberid are alphanumerics and all caps so I had to conver all to upper case, else, comment the above strtoupper call

//get Downline of a Member, this function is needed so that you can simply call left or right of the memberid you are looking for
		function GetDownline($member_id,$direction) 
		{ 
				$getdownlinesql = @mysql_fetch_assoc(@mysql_query('select memid,placementid,position from `yourtablehere` where placementid="'.$member_id.'" and  position="'.$direction.'"'));
				$getdownline = $getdownlinesql['memid'];
				return $getdownline; 
		}

//get the child of the member, this section will look for left or right of a member, once found, it will call GetNextDownlines() function to assign new memberid variables for left or right
    function GetChildDownline($member_id) 
    { 
				$getchilddownlinesql = @mysql_query('select memid,placementid,position from `yourtablehere` where placementid="'.$member_id.'" ORDER BY position');
				while($childdownline = mysql_fetch_array($getchilddownlinesql)){
						$childdownlinecode = $childdownline['memid'];
						$direction = $childdownline['position'];
						if($direction=='L'){
							if($childdownlinecode){
								//this is where you play with your html layout
								echo $childdownlinecode.'<br>';
								GetNextDownlines($childdownlinecode,'L');
							}
						}
						
							if($direction=='R'){
								if($childdownlinecode){
									//this is where you play with your html layout
									echo $childdownlinecode.'<br>';
									GetNextDownlines($childdownlinecode,'R');
							}
						}
				 }
    }

//recursive function to call the functions and start all over again, this is where you can get the newly assigned memberid, call the GetChildDownline() that gets the left or right, then recycle all codes
    function GetNextDownlines($member_id,$direction) 
    {
			if($direction=='L'){
					$topleft = GetDownline($member_id,'L');
						if($topleft){
								//this is where you play with your html layout
						echo $topleft.'<br>';
						}
							$getleftdownlinesql = @mysql_query('select memid,placementid,position from `yourtablehere` where placementid="'.$topleft.'" ORDER BY position');
							while($getleftdownline = mysql_fetch_array($getleftdownlinesql)){
							$leftdownline = $getleftdownline['memid'];
							$leftdirection = $getleftdownline['position'];
							
							if($leftdirection=='L'){
									if($leftdownline){
								//this is where you play with your html layout
										echo $leftdownline.'<br>';
										GetChildDownline($leftdownline);
									}
							  }
							
							if($leftdirection=='R'){
									if($leftdownline){
								//this is where you play with your html layout
									echo $leftdownline.'<br>';
									 GetChildDownline($leftdownline);
									 }
							   }
							 }
			}
			
			
			if($direction=='R'){
						$topright = GetDownline($member_id,'R');
						if($topright){
						echo $topright.'<br>';
						}
						$getrightdownlinesql = @mysql_query('select memid,placementid,position from `yourtablehere` where placementid="'.$topright.'" ORDER BY position');
						while($getrightdownline = @mysql_fetch_array($getrightdownlinesql)){
									$rightdownline = $getrightdownline['memid'];
									$rightdirection = $getrightdownline['position'];
									
									if($rightdirection=='L'){
											if($rightdownline){
								//this is where you play with your html layout
											echo $rightdownline.'<br>';
											GetChildDownline($rightdownline);
											}
									}
									
									if($rightdirection=='R'){
											if($rightdownline){
								//this is where you play with your html layout
											echo $rightdownline.'<br>';
											GetChildDownline($rightdownline);
											}
									}
						
							}
				}
}

?>
<html>
	<head>
	<title>Genealogy</title>
	<meta http-equiv=Content-Type content="text/html; charset=utf-8">
	<meta http-equiv=content-language content=en>
	<link href="styles.css" type=text/css rel=stylesheet>
	</head>
<body>
<table cellpadding="0" cellspacing="0" width="100%" border="0" class="noborder">
 <tr>
	<td>
<?php
echo $topmcode.'<br>';
GetNextDownlines($topmcode,'L');
GetNextDownlines($topmcode,'R');
?>

	</td>
 </tr>
</table>
</body>
</html>

This is not the full code, but it gives you insights on how to do full recursive view on your data structure. Also, this is a product of 3:30 AM with no sleep yet so it maybe sloppy and redundant... hehe

If you can make it thinner or smaller it would be better, or if there is a real way to recycles codes without my redundancies....

I hope this helps

[Atli]

Hey.
Sorry it took me so long to respond. Been busy.

Anyhow, here is my take on this problem.

Positioning the IDs themselves can be done fairly easily with HTML, but the lines between parent-child IDs aren't as easily created.

I created this, which creates a HTML hierarchy of <div> elements, which positions the IDs with their parents. I over-commented the code, so I won't explain to much.

<?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 = "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($dbName, $tblName) || (empty($dbName) && 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; background-color: {$this->bgColor}; }
    div#{$divID} p   { margin: 0; padding: 0; }
</style>
<div id="{$divID}">
HTML;

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

            // 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 = -1;
        }
        $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><p>{$_id}</p>";
            $this->buildHtml($_children, $output);
            $output .= "</div>";
        }
    }
}
?>

Which you could use like so:

<!DOCTYPE html>
<html>
    <head>
        <title>Tree-view Test</title>
        <meta http-equiv="Content-Type" content="text/html; charset=utf8">
    </head>
    <body>
        <?php
        $dbLink = new mysqli("localhost", "usr", "pwd", "dbName");

        $treeView = new TreeView($dbLink);
        $treeView->printTree('tblName');

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

This won't draw lines between the IDs, but it will position them nicely. I though about using JavaScript to draw the lines, but I'm way to tired for that at the moment xD

P.S.
Don't try this using Internet Explorer. It has a problem rendering CSS rules defined this decade, so the background colors won't show.

[hemgoyal_1990]

Hi,

You can try to approach this in different ways, however, you will need to see how you would layout your page too. Based on my experience, the layout you were looking at will grow wider so you will need to limit how much you can show per page.

I will try to give you 2 approaches I can think of that might work for you.

First, limit your display up to 15 IDs only, this will allow you to put as much details as you need per ID, please refer to the illustration taken from my genealogy system:

http://i34.tinypic.com/j9x63b.jpg

This method will allow your database to breathe from over-processing because of recursive calls.
What I did with this was just layout an html page and used a function call to get left and right till you fill all 15 slots. Here is the php code I use:

function GetDownline($member_id,$direction) 
    { 
$getdownlinesql = @mysql_fetch_assoc(@mysql_query('select memberid,placementid,position from `yourrelationaltable` where placementid="'.$member_id.'" and  position="'.$direction.'"'));
$getdownline = $getdownlinesql['memberid'];
return $getdownline; 
    }

then simply call it with:
$firstleft = GetDownline('headmemberidhere','Left'); //for first left
$firstright = GetDownline('headmemberidhere','Right'); //for first right
echo $firstleft;
echo $firstright;

Now for the next left or right

$secondleftofleft = GetDownline($firstleft,'Left'); //for second left of first left
$secondrightofleft = GetDownline($firstleft,'Right'); //for second right of first left

echo $secondleftofleft;
echo $secondrightofleft;

$secondleftofright = GetDownline($firstright,'Left'); for second left of first right
$secondrightofright = GetDownline($firstright,'Right'); for second right of first right

echo $secondleftofright;
echo $secondrightofright;

..And so on, now you can build your 15 tree line on a page, to give the effect of going downline, you can make all of the buttons clickable carrying their own member id's so it makes it the top of the tree, just to a GET or POST method to assign a new value for headmemberidhere that serves as your head of the tree.

Note:
This is not my exact code and is for illustration purposed only, but the code will work as is.

That is the simplest method to have a nice layout of the binary tree.

The next one is the full recursive method similar to Atli's post.

However, this approach is not recommended if you are on a shared server or have a large database like I have.

The full recursive method I did was a bit complicated to get the effect due to the nature of my database structure. I actually had an existing database to work on so I had difficulties adapting or creating the proper code since I had to adapt to the database instead of me creating the perfect code that will do a simple recursive structure.

Below is the method I used to make recursion work from top to bottom of the tree in full view.

I did three function calls to so that each set will call different codes at the same time in recursion, I am not sure how to do this easily too because of the existing database structure.

Here is an illustrative php code for you:

<?php
include("../includes/config.php"); 
//my database connection is set in my config, otherwise, just create your own db connect
$defaultmcode = 'yourdefaultidhere';
if($_GET['topmcode']){
$topmcode = trim($_GET['topmcode']);
}else{
$topmcode = $defaultmcode;
}
$topmcode = ltrim($topmcode);
$topmcode = rtrim($topmcode);
$topmcode = strtoupper($topmcode);
//my memberid are alphanumerics and all caps so I had to conver all to upper case, else, comment the above strtoupper call

//get Downline of a Member, this function is needed so that you can simply call left or right of the memberid you are looking for
		function GetDownline($member_id,$direction) 
		{ 
				$getdownlinesql = @mysql_fetch_assoc(@mysql_query('select memid,placementid,position from `yourtablehere` where placementid="'.$member_id.'" and  position="'.$direction.'"'));
				$getdownline = $getdownlinesql['memid'];
				return $getdownline; 
		}

//get the child of the member, this section will look for left or right of a member, once found, it will call GetNextDownlines() function to assign new memberid variables for left or right
    function GetChildDownline($member_id) 
    { 
				$getchilddownlinesql = @mysql_query('select memid,placementid,position from `yourtablehere` where placementid="'.$member_id.'" ORDER BY position');
				while($childdownline = mysql_fetch_array($getchilddownlinesql)){
						$childdownlinecode = $childdownline['memid'];
						$direction = $childdownline['position'];
						if($direction=='L'){
							if($childdownlinecode){
								//this is where you play with your html layout
								echo $childdownlinecode.'<br>';
								GetNextDownlines($childdownlinecode,'L');
							}
						}
						
							if($direction=='R'){
								if($childdownlinecode){
									//this is where you play with your html layout
									echo $childdownlinecode.'<br>';
									GetNextDownlines($childdownlinecode,'R');
							}
						}
				 }
    }

//recursive function to call the functions and start all over again, this is where you can get the newly assigned memberid, call the GetChildDownline() that gets the left or right, then recycle all codes
    function GetNextDownlines($member_id,$direction) 
    {
			if($direction=='L'){
					$topleft = GetDownline($member_id,'L');
						if($topleft){
								//this is where you play with your html layout
						echo $topleft.'<br>';
						}
							$getleftdownlinesql = @mysql_query('select memid,placementid,position from `yourtablehere` where placementid="'.$topleft.'" ORDER BY position');
							while($getleftdownline = mysql_fetch_array($getleftdownlinesql)){
							$leftdownline = $getleftdownline['memid'];
							$leftdirection = $getleftdownline['position'];
							
							if($leftdirection=='L'){
									if($leftdownline){
								//this is where you play with your html layout
										echo $leftdownline.'<br>';
										GetChildDownline($leftdownline);
									}
							  }
							
							if($leftdirection=='R'){
									if($leftdownline){
								//this is where you play with your html layout
									echo $leftdownline.'<br>';
									 GetChildDownline($leftdownline);
									 }
							   }
							 }
			}
			
			
			if($direction=='R'){
						$topright = GetDownline($member_id,'R');
						if($topright){
						echo $topright.'<br>';
						}
						$getrightdownlinesql = @mysql_query('select memid,placementid,position from `yourtablehere` where placementid="'.$topright.'" ORDER BY position');
						while($getrightdownline = @mysql_fetch_array($getrightdownlinesql)){
									$rightdownline = $getrightdownline['memid'];
									$rightdirection = $getrightdownline['position'];
									
									if($rightdirection=='L'){
											if($rightdownline){
								//this is where you play with your html layout
											echo $rightdownline.'<br>';
											GetChildDownline($rightdownline);
											}
									}
									
									if($rightdirection=='R'){
											if($rightdownline){
								//this is where you play with your html layout
											echo $rightdownline.'<br>';
											GetChildDownline($rightdownline);
											}
									}
						
							}
				}
}

?>
<html>
	<head>
	<title>Genealogy</title>
	<meta http-equiv=Content-Type content="text/html; charset=utf-8">
	<meta http-equiv=content-language content=en>
	<link href="styles.css" type=text/css rel=stylesheet>
	</head>
<body>
<table cellpadding="0" cellspacing="0" width="100%" border="0" class="noborder">
 <tr>
	<td>
<?php
echo $topmcode.'<br>';
GetNextDownlines($topmcode,'L');
GetNextDownlines($topmcode,'R');
?>

	</td>
 </tr>
</table>
</body>
</html>

This is not the full code, but it gives you insights on how to do full recursive view on your data structure. Also, this is a product of 3:30 AM with no sleep yet so it maybe sloppy and redundant... hehe

If you can make it thinner or smaller it would be better, or if there is a real way to recycles codes without my redundancies....

I hope this helps

Thank You For Your Help..
Your Code is Work Fine and Very Helpful for Me.
but I Have Some Trouble in The Above Code. I Have Created a Page Called http://www.liferider.info/mlm/tree.php This Page Create Tree View from Your Above Given Code.
This Code Have a Bug That This Print all Node in Every Section of Binary Tree.
Please Check the above Page for Illustration.

Please Tell me How I Can Print only Child Node in Binary Tree. my Database Schema is Given Below:

|  memid  |  |   placementid  |  |   position  |
|      1  |  |        0     |  |        0        |
|      2  |  |        1     |  |        L        |
|      3  |  |        1     |  |        R        |
|      4  |  |        2     |  |        L        |
|      5  |  |        2     |  |        R        |
|      6  |  |        3     |  |        L        |
|      7  |  |        3     |  |        R        |
|      8  |  |        4     |  |        L        |
|      9  |  |        4     |  |        R        |
|     10 |  |        5     |  |        L        |
|     11 |  |        5     |  |        R        |
|     12 |  |        6     |  |        L        |
|     13 |  |        6     |  |        R        |
|     14 |  |        7     |  |        L        |
|     15 |  |        7     |  |        R        |

[hemgoyal_1990]

Please Help..
I am Very Much Need Help...

[hemgoyal_1990]

Hey.
Sorry it took me so long to respond. Been busy.

Anyhow, here is my take on this problem.

Positioning the IDs themselves can be done fairly easily with HTML, but the lines between parent-child IDs aren't as easily created.

I created this, which creates a HTML hierarchy of <div> elements, which positions the IDs with their parents. I over-commented the code, so I won't explain to much.

<?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 = "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($dbName, $tblName) || (empty($dbName) && 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; background-color: {$this->bgColor}; }
    div#{$divID} p   { margin: 0; padding: 0; }
</style>
<div id="{$divID}">
HTML;

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

            // 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 = -1;
        }
        $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><p>{$_id}</p>";
            $this->buildHtml($_children, $output);
            $output .= "</div>";
        }
    }
}
?>

Which you could use like so:

<!DOCTYPE html>
<html>
    <head>
        <title>Tree-view Test</title>
        <meta http-equiv="Content-Type" content="text/html; charset=utf8">
    </head>
    <body>
        <?php
        $dbLink = new mysqli("localhost", "usr", "pwd", "dbName");

        $treeView = new TreeView($dbLink);
        $treeView->printTree('tblName');

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

This won't draw lines between the IDs, but it will position them nicely. I though about using JavaScript to draw the lines, but I'm way to tired for that at the moment xD

P.S.
Don't try this using Internet Explorer. It has a problem rendering CSS rules defined this decade, so the background colors won't show.

Friedn Could You Tell me That Where I Put Table name in The Above Code.
I am Created a Table Called mlm_data with Below Field:

ID         parentID
1                0
2                1
3                1
4                2
5                2
6                3
7                3
8                4
9                4
10              5
11              5
12              6
13              6
14              7
15              7

Please Help me It's Very Urgent.

[Atli]

Friedn Could You Tell me That Where I Put Table name in The Above Code.

In line #12 of the second code, where it says 'tblName' ;-)

[hemgoyal_1990]

In line #12 of the second code, where it says 'tblName' ;-)

I Put the Table name where u specified and run my code but there nothing print..
in below url format:
http://yourdomain.com/mlm/tree.php

is there the above url is right or may change the url.

[Atli]

Did you include the class or just put it at the top of the page?

Ideally, you would put the class (the first code I posted in post #10) into a file, lets call it "class.TreeView.php".

Then you create your tree file, "tree.php", and you put this into it:

<!DOCTYPE html>
<html>
    <head>
        <title>Tree-view Test</title>
        <meta http-equiv="Content-Type" content="text/html; charset=utf8">
    </head>
    <body>
        <?php
        // Turn on error reporting, just in case.
        ini_set('display_errors', true);
        error_reporting(E_ALL);
 
        // 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('tblName');

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

In that code, you need to fill out both the MySQL database info and replace the 'tblName' with the name of your table.

[Atli]

Ohh, I just realized, there is a bug in my class.

Line #60. Replace it with:

if(!isset($tblName) || empty($tblName))

Minor oversight. Sorry ;-)

[hemgoyal_1990]

i am compile my code with your guideline and follow that there are nothing print. i think there are some mistake in my database structure.
i am using the below table structure:

id         parentID
1                0
2                1
3                1
4                2
5                2
6                3
7                3
8                4
9                4
10              5
11              5
12              6
13              6
14              7
15              7

[Atli]

Yea, I see.
My example uses -1 as the parentID for root-level rows. You need to either change the value in your database or alter the code to use 0 instead (you can do that on line #117 of the code).

[hemgoyal_1990]

Thanx Atil for Helping me Out.

I am Very Thankful for You. You Solve my Very big Problem.