Originally Posted by 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:

Help with Code Tags

PHP Syntax (Toggle Plain Text)

    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;

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

Help with Code Tags

PHP Syntax (Toggle Plain Text)

$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;

$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:

Help with Code Tags

PHP Syntax (Toggle Plain Text)

<?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>

<?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

Originally Posted by 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.

Help with Code Tags

php Syntax (Toggle Plain Text)

<?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>";
        }
    }
}
?>

<?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:

Help with Code Tags

php Syntax (Toggle Plain Text)

<!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>

<!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.

Originally Posted by hemgoyal_1990

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

Originally Posted by Atli

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