MLM binary tree structure
And have you relevant code/queries that you have tried?
Thank you!
Laat even alle relevante code, structuren en queries zien.
Gewijzigd op 04/11/2018 19:03:53 door - Ariën -
Code (php)
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
<?php
//include the treeview class
include 'treeview.php';
//create an instant of Treeview Class
$treeSample = new Treeview('localhost','root','','smartlife');
//Calling the method to generate tree view and set the queryArray public member for Input Parameter
$treeSample->CreateTreeview('registration','id','name','mobile', 'parent_id', 'perfixForJqueryIDs');
//echo the public member of object names treeResult (Contain the treeview html and jquery codes)
echo $treeSample->treeResult;
?>
//include the treeview class
include 'treeview.php';
//create an instant of Treeview Class
$treeSample = new Treeview('localhost','root','','smartlife');
//Calling the method to generate tree view and set the queryArray public member for Input Parameter
$treeSample->CreateTreeview('registration','id','name','mobile', 'parent_id', 'perfixForJqueryIDs');
//echo the public member of object names treeResult (Contain the treeview html and jquery codes)
echo $treeSample->treeResult;
?>
Toevoeging op 04/11/2018 19:19:28:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
<?php
Class Treeview
{
public
$treeResult
,$prefix
;
private
$queryArray
,$dbh
;
public function __construct($db_host, $db_username, $db_password, $db_name)
{
/* Create connection only if not exist and store in $dbh (Database Handler) private member */
if(!isset($this->dbh))
{
try
{
/* Create connection */
$this->dbh = mysqli_connect($db_host,$db_username, $db_password, $db_name);
/* change character set to utf8 */
mysqli_set_charset($this->dbh, "utf8");
}
catch (Exception $error)
{
/* Create and output an error if connecting faild*/
trigger_error("Something happend and not connected to database");
return $error;
}
}
}
/**
* @param int $parent
* @param $tablename the target table name
* @param string $primaryKeyField (Target database table primary key (it is usually id))
* @param string $titleFieldName (Title field of database table that will appears in treeview items)
* @param string $parentRelationFieldName (The field that is used to create relation between items)
* @param string $jqueryNoConfilictPerfix (When we have multiple treeview(s) in a single web page we need this prefix to prevent of HTML same id errors )
*/
public function CreateTreeview($tablename, $primaryKeyField = 'id', $titleFieldName = 'name', $mobile = 'mobile', $parentRelationFieldName = 'parent_id', $jqueryNoConfilictPerfix = 'tree_1')
{
$result = mysqli_query($this->dbh, "Select $primaryKeyField, $titleFieldName, $mobile, $parentRelationFieldName FROM $tablename" ) ;
while ($row = mysqli_fetch_assoc($result))
{
//
// Wrap the row array in a parent array, using the id as they key
// Load the row values into the new parent array
//
$this->queryArray[$row['id']] = array(
'id' => $row[$primaryKeyField],
'name' => $row[$titleFieldName],
'mobile' => $row[$mobile],
'parent_id' => $row[$parentRelationFieldName]
);
}
$this->generate_tree_list($this->queryArray);
}
private function generate_tree_list($array, $parent = 0)
{
//
// Reset the flag each time the function is called
//
$has_children = false;
//
// Loop through each item of the list array
//
foreach($array as $key => $value)
{
//
// For the first run, get the first item with a parent_id of 0 (= root category)
// (or whatever id is passed to the function)
//
// For every subsequent run, look for items with a parent_id matching the current item's key (id)
// (eg. get all items with a parent_id of 2)
//
// This will return false (stop) when it find no more matching items/children
//
// If this array item's parent_id value is the same as that passed to the function
// eg. [parent_id] => 0 == $parent = 0 (true)
// eg. [parent_id] => 20 == $parent = 0 (false)
//
if ($value['parent_id'] == $parent)
{
//
// Only print the wrapper ('<ul>') if this is the first child (otherwise just print the item)
// Will be false each time the function is called again
//
if ($has_children === false)
{
//
// Switch the flag, start the list wrapper, increase the level count
//
$has_children = true;
$this->treeResult .= " <ul class='parent insRootClose'>" ;
}
{$this->treeResult .= '<li><ins onclick="expandNode(this.id);"' . "id='$this->prefix" . $value['id'] . "'" . '> </ins>' . $value['name'] . " " . '- </ins>' . $value['mobile'];}
$this->generate_tree_list($array, $key);
//
// Close the item
//
$this->treeResult .= '</li>';
}
}
//
// If we opened the wrapper above, close it.
//
if ($has_children === true) $this->treeResult .= '</ul>';
}
public function __destruct()
{
}
}
?>
Class Treeview
{
public
$treeResult
,$prefix
;
private
$queryArray
,$dbh
;
public function __construct($db_host, $db_username, $db_password, $db_name)
{
/* Create connection only if not exist and store in $dbh (Database Handler) private member */
if(!isset($this->dbh))
{
try
{
/* Create connection */
$this->dbh = mysqli_connect($db_host,$db_username, $db_password, $db_name);
/* change character set to utf8 */
mysqli_set_charset($this->dbh, "utf8");
}
catch (Exception $error)
{
/* Create and output an error if connecting faild*/
trigger_error("Something happend and not connected to database");
return $error;
}
}
}
/**
* @param int $parent
* @param $tablename the target table name
* @param string $primaryKeyField (Target database table primary key (it is usually id))
* @param string $titleFieldName (Title field of database table that will appears in treeview items)
* @param string $parentRelationFieldName (The field that is used to create relation between items)
* @param string $jqueryNoConfilictPerfix (When we have multiple treeview(s) in a single web page we need this prefix to prevent of HTML same id errors )
*/
public function CreateTreeview($tablename, $primaryKeyField = 'id', $titleFieldName = 'name', $mobile = 'mobile', $parentRelationFieldName = 'parent_id', $jqueryNoConfilictPerfix = 'tree_1')
{
$result = mysqli_query($this->dbh, "Select $primaryKeyField, $titleFieldName, $mobile, $parentRelationFieldName FROM $tablename" ) ;
while ($row = mysqli_fetch_assoc($result))
{
//
// Wrap the row array in a parent array, using the id as they key
// Load the row values into the new parent array
//
$this->queryArray[$row['id']] = array(
'id' => $row[$primaryKeyField],
'name' => $row[$titleFieldName],
'mobile' => $row[$mobile],
'parent_id' => $row[$parentRelationFieldName]
);
}
$this->generate_tree_list($this->queryArray);
}
private function generate_tree_list($array, $parent = 0)
{
//
// Reset the flag each time the function is called
//
$has_children = false;
//
// Loop through each item of the list array
//
foreach($array as $key => $value)
{
//
// For the first run, get the first item with a parent_id of 0 (= root category)
// (or whatever id is passed to the function)
//
// For every subsequent run, look for items with a parent_id matching the current item's key (id)
// (eg. get all items with a parent_id of 2)
//
// This will return false (stop) when it find no more matching items/children
//
// If this array item's parent_id value is the same as that passed to the function
// eg. [parent_id] => 0 == $parent = 0 (true)
// eg. [parent_id] => 20 == $parent = 0 (false)
//
if ($value['parent_id'] == $parent)
{
//
// Only print the wrapper ('<ul>') if this is the first child (otherwise just print the item)
// Will be false each time the function is called again
//
if ($has_children === false)
{
//
// Switch the flag, start the list wrapper, increase the level count
//
$has_children = true;
$this->treeResult .= " <ul class='parent insRootClose'>" ;
}
{$this->treeResult .= '<li><ins onclick="expandNode(this.id);"' . "id='$this->prefix" . $value['id'] . "'" . '> </ins>' . $value['name'] . " " . '- </ins>' . $value['mobile'];}
$this->generate_tree_list($array, $key);
//
// Close the item
//
$this->treeResult .= '</li>';
}
}
//
// If we opened the wrapper above, close it.
//
if ($has_children === true) $this->treeResult .= '</ul>';
}
public function __destruct()
{
}
}
?>
Waar loop je nu precies op vast, en wat is nu je specifieke vraag?
Wat gebeurt er nu? Wat zie je? Wat doe je? Wat verwacht je wat je code doet?
Je geeft nu enkel een lap code, en daar kunnen we weinig mee.
Gewijzigd op 04/11/2018 19:21:50 door - Ariën -
Over de hele boom, als je zorgt dat je de queryresultaten sorteert op parent_id kun je daarna een recursieve functie schrijven die alles aan elkaar knoopt. Dus in wezen wat je in generate_tree_list doet, maar daar is het misschien handiger om eerst een datastructuur op te bouwen, en deze dan later uit te printen met behulp van deze (interne) structuur. Op die manier splits je de verantwoordelijkheden tussen verschillende methoden (separation of concerns).
Je kunt dit ook nog verder opsplitsen door bijvoorbeeld deze interne datastructuur nog een keer te filteren op een specifiek user id, je bereikt dan in principe hetzelfde als het bovenstaande, maar met generiekere bouwstenen. In die methode zou je ook een argument door kunnen geven die aangeeft hoeveel subniveau's jke wilt zien. Voor de directe afstammelingen zou je dan een "diepte" van één niveau kunnen gebruiken. Op deze manier heb je ook je vraagstuk van de database naar de datastructuur verplaatst waarbij je de gevonden resultaten direct in deze structuur kunt aanpassen.
----
I don't quite see how this is a binary tree? I mean someone could probably sign up more than one or two people? Wouldn't it be better to select the direct descendants / children of the user, so something like WHERE parent_id = <user id> if that's the only info you wish to obtain? There's no real need to churn out an entire tree for that?
About the tree itself: as long as you sort the query results by parent_id you can write a recursive function that hooks up everything to itself. Kind of what you are doing in the generate_tree_list method, but there it might be a better idea to build an internal data structure first, and use this structure later on to print some tree. That way you split different responsibilities into different methods (separation of concerns).
You could even split things up further, for example by writing a method to filter this data structure on specific arguments such as a user id. That way you accomplish the same as the specific query mentioned before, but with more generic building blocks. In that method you could also pass an argument that indicates how many levels (subtrees) you want to see. For direct descendants you could use a "depth" of one level. With this approach you also moved your query ("who are the direct descendants of user X") from the database to your data structure so you can further manipulate results locally.
Gewijzigd op 04/11/2018 22:00:21 door Thomas van den Heuvel