Trying to create a binary tree but my downline is not in the correct order

Question

I am trying to create a binary tree using PHP, MySQL and a jQuery plugin by Frank-Mich

This is what I have so far...

DATABASE STRUCTURE

CREATE TABLE IF NOT EXISTS `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `regDate` date NOT NULL,
  `memberID` varchar(20) NOT NULL,
  `sponsor` int(10) NOT NULL,
  `position` varchar(5) NOT NULL,
  `firstname` varchar(50) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

INSERT INTO `members` (`id`, `regDate`, `memberID`, `sponsor`, `position`, `firstname`, `lastname`) VALUES
(1, '2016-10-01', 'S0000000', 0, '', 'Fred', NULL),
(2, '2016-10-02', 'S5483002', 1, '', 'John', NULL),
(3, '2016-10-03', 'S6182013', 2, 'Lt', 'Innorlito', NULL),
(4, '2016-10-03', 'S6325681', 18, 'Lt', 'Sarah', NULL),
(5, '2016-10-04', 'S6329592', 3, 'Lt', 'Imelda', NULL),
(6, '2016-10-06', 'S6345140', 5, 'Rt', 'Diane', NULL),
(7, '2016-10-05', 'S6325799', 4, 'Lt', 'John III', NULL),
(8, '2016-10-05', 'S6356038', 4, 'Rt', 'Myda', NULL),
(9, '2016-10-07', 'S6358217', 7, 'Lt', 'Rondale', NULL),
(10, '2016-10-07', 'S6506318', 7, 'Rt', 'Rosana', NULL),
(11, '2016-10-08', 'S6356151', 8, 'Lt', 'Wiljames', NULL),
(12, '2016-10-08', 'S6456156', 8, 'Rt', 'Mark', NULL),
(13, '2016-10-09', 'S6379249', 16, 'Rt', 'Maria Fe', NULL),
(14, '2016-10-09', 'S6345177', 6, 'Lt', 'Penafrancia', NULL),
(15, '2016-10-10', 'S6345204', 6, 'Rt', 'Ruth', NULL),
(16, '2016-10-04', 'S6345083', 5, 'Lt', 'Maricel', NULL),
(17, '2016-10-11', 'S6345905', 16, 'Lt', 'Elena', NULL),
(18, '2016-10-18', 'S6320374', 2, 'Rt', 'Myrna', NULL),
(19, '2016-10-19', 'S6383350', 3, 'Rt', 'Reynaldo', NULL),
(20, '2016-10-17', 'S6383593', 19, 'Lt', 'Narcisa', NULL),
(21, '2016-10-17', 'S6510270', 19, 'Rt', 'Juanita', NULL),
(22, '2016-11-02', 'S6384918', 20, 'Lt', 'Arsenia', NULL),
(23, '2016-11-02', 'S6385105', 20, 'Rt', 'Zusara', NULL),
(24, '2016-11-04', 'S6511807', 21, 'Lt', 'Vivian', NULL);

memberID - is alpha-numeric, used for display only
sponsor - is the "parent ID" for that child record
position - is the placement in the binary structure, Left or Right

PHP

if(!isset($_SESSION['MM_id']) || $_SESSION['MM_id'] == '') {
    $id=2;
} else {
    $id = $_SESSION['MM_id'];
}

// pull the "head" record... "top" of the binary
$sql = "SELECT * FROM members WHERE `id`=:id";
$query = $conn->prepare($sql);
$query->bindValue(':id', $id, PDO::PARAM_INT);
$query->execute();
$result = $query->fetch(PDO::FETCH_ASSOC);
$totalRows = $query->rowCount();

// pull all records (children) of the top record and group them into their parent-child relationships
$sql = "SELECT * FROM members WHERE `id` > :id ORDER BY `sponsor` ASC, `id` ASC";
$query_rsMembers = $conn->prepare($sql);
$query_rsMembers->bindValue(':id', $id, PDO::PARAM_INT);
$query_rsMembers->execute();
$result_rsMembers = $query_rsMembers->fetch(PDO::FETCH_ASSOC);
$totalRows_rsMembers = $query_rsMembers->rowCount();

$tree = ''; // create an empty tree variable
$used = ''; // create an empty used variable
//echo 'This is the order that the records were pulled<br>';
do {
    $sid = $result_rsMembers['sponsor'];
    $sql = "SELECT * FROM members WHERE `sponsor` = :sid AND `id` != :used ORDER BY `position`";
    $query_rsTree = $conn->prepare($sql);
    $query_rsTree->bindValue(':sid', $sid, PDO::PARAM_STR);
    $query_rsTree->bindValue(':used', $used, PDO::PARAM_INT);
    $query_rsTree->execute();
    $result_rsTree = $query_rsTree->fetch(PDO::FETCH_ASSOC);
    $totalRows_rsTree = $query_rsTree->rowCount();
    $used = $result_rsTree['id'];
    $tree .= '<div align="center" style="border-style: inset; margin:0 0 10px 10px; font-family:Arial, Helvetica, sans-serif; font-size:12px;"><span>'.$result_rsTree['firstname'].'<br>'.$result_rsTree['memberID'].'<br>'.$result_rsTree['regDate'].'</span></div>';
    //echo $result_rsTree['firstname'].'<br>';
} while ($result_rsMembers = $query_rsMembers->fetch(PDO::FETCH_ASSOC));

$timenow = time() + 28800;

HTML

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Binary Tree</title>
<script type="text/javascript" src="inc/wz_jsgraphics.js?v=<?php echo $timenow ?>"></script>
<script type="text/javascript" src="../js/jquery/jquery-1.12.4.js?v=<?php echo $timenow ?>"></script>
<script type="text/javascript" src="inc/jquery.btree.js?v=<?php echo $timenow ?>"></script>
<script type="text/javascript">
var myTree;
$(document).ready(function(){
    myTree = $("#treeDiv").btree()[0];
});

function mich(){
    $("#box1").css("left", "");
    alert($("#box1").css("left") + " - " + $("#box1").css("right"));
}
</script>
</head>

<body>
<div id="treeDiv" style="vertical-align:text-top; overflow:hidden; min-height:400px; min-width:1000px; border-style:solid; border-width: 3px; border-color: #000000;">
    <?php echo $tree ?>
    <div align="center" style="border-style: inset; margin:0 0 10px 10px; font-family:Arial, Helvetica, sans-serif; font-size:12px;" id="box1">
        <span><?php echo $result['firstname'].'<br>'.$result['memberID'].'<br>'.$result['regDate'] ?></span>
    </div>
</div>
</body>
</html>

The problem I am having is creating the tree in the proper order of parent-child (left) and parent-child right.

This image shows the way that the tree is created...

This is the way it should look...

It appears to be pulling the records in the correct order and grouping them in their correct parent child relationship; however, when it begins to build the tree it simply creates left floating divs starting from left to right.

Try as I may, I cannot seem to get them display correctly.

I hope someone can help with this.

score 1 · Accepted Answer · answered Nov 04 '16 at 12:23

First of all, for the plugin to work, your tree would have to be complete/full. From your result you can see that the script drew the beginning of a gapless tree from left to right, but then stopped when it ran out of new elements.

The current code doesn't allow for gaps, but it looks like it would be possible to adjust the plugin script source code to not draw missing boxes (and lines), but you would still need to add a (marked or empty) <div> for them, so you would need to know if you have a gap somewhere (including empty elements for all children that element might have).

The second problem is that you don't get the required order from your data. For your sample data, you would need the order: 1. Elena, 2. Maricel 3. Maria Fe 4.Imelda, 5.Penafranica 6. Diane 7. Ruth, ..., which is not that easy to get. The default data structure for that would be a threaded binary tree, you can e.g. convert your binary tree in php after retrieving the whole binary tree, and if you do that in an array, you can automatically get your gaps too; or you you can add that structure (and the required update-code) to your current tree.

But you should probably just look for a different code or plugin that better fits your requirements. A google search for ancestor tree html or family tree html should give you some alternatives. (Without testing or assessment, the first result CSS3 Family Tree looks like it might work).

Thank you for the CSS3 Family Tree suggestion. It took quite a while to create queries that would get my lineage in the correct order (as required by CSS3 Family Tree), but I eventually got it and everything works wonderfully. The display is also nice on the eyes. Thank you! — Kuya, Dec 18 '16 at 04:38

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

If you are looking for a MLM style representation, check this link it might help How to generate a tree for MLM ( Multi-Level Marketing ) in JavaScript for either JSON / XML data

edited May 23 '17 at 11:45

Community

1
1

answered Nov 04 '16 at 12:52

jitishp

11
1

Thank you for the suggestion, but the visible presentation of the accepted answer leaves a lot to be desired to say the least. If you have any other suggestions, I am open to them. – Kuya Nov 26 '16 at 15:16

Trying to create a binary tree but my downline is not in the correct order

2 Answers2