Updating a tree structure

[TheServant]

Hi Guys,
I am trying to get some good logic down on paper, but before I waste my time with this path of thinking I was wondering if you can tell me any majory drawbacks to the logic.

Imagine a tree structure,
A / B C
B / D E
D / F G
E / H I

_____________A________
________B_________C___
____D_______E_________
__F___G___H___I_______


Everyone under A will be in Group A. Now if I move B from being under A to being under noone, he (and all those under him) will be in group B. I am trying to automate this as those under B will vary in number and structure. Each person has a superior (that is how the database works):

Logic:
[PHP]function change_superior(person, new_group) {
mysql_query("UPDATE table1
SET group = $new_group
WHERE name = $person");

foreach( mysql_query("SELECT * FROM table1 WHERE superior=$person") as $under ) {
change_superior($under,$new_group);
}
}
[/PHP]

Quite shakey on foreach loops so my syntax is probably way off, but let me know what you think.

[pbmods]

Heya, TheServant.

mysql_query() returns a resource that you run through mysql_fetch_object() to retrieve your rows (http://php.net/mysql_fetch_object):

$result = mysql_query('...');
while( $row = mysql_fetch_object($result) )
{
  process_row($row);
}
 

[Atli]

You don't really need the "group" column there, do you?

I mean, if I were to map a path to person "F", it would look like so:
"A->B->D->F".

From that we can deduce that F belongs to A, as A is it's the top-level parent.
If you were to change B's parent to null, F's top-level parent would become B, which automatically achieves your goal, doesn't it?

In practice, you would only have to assign each person a "parent", and to find which group it belongs to, you would just have to climb to the top-level parent.

Somewhat like:

function getTopLevelParent($person) {
  $sql = "SELECT parent FROM tbl WHERE person = '$person'";
  $result  = mysql_query($sql);
  $row = mysql_fetch_row($result);
 
  if($row['parent'] != null)
    return getTopLevelParent($row['parent']);
  else
    return $row['parent'];
}
 

[TheServant]

Heya, TheServant.

mysql_query() returns a resource that you run through mysql_fetch_object() to retrieve your rows (http://php.net/mysql_fetch_object):

$result = mysql_query('...');
while( $row = mysql_fetch_object($result) )
{
  process_row($row);
}
 

Cheers, knew it was something like that but don't have my old code to go through where I have used it before. What about the actual function? Is it calling itself ok? No glaring problems you can see?

And with teh loop, should it be:
$result = mysql_query('...');
foreach( $row = mysql_fetch_object($result) )
?

Thanks for your reply.

[TheServant]

You don't really need the "group" column there, do you?

I mean, if I were to map a path to person "F", it would look like so:
"A->B->D->F".

From that we can deduce that F belongs to A, as A is it's the top-level parent.
If you were to change B's parent to null, F's top-level parent would become B, which automatically achieves your goal, doesn't it?

In practice, you would only have to assign each person a "parent", and to find which group it belongs to, you would just have to climb to the top-level parent.

Somewhat like:

function getTopLevelParent($person) {
  $sql = "SELECT parent FROM tbl WHERE person = '$person'";
  $result  = mysql_query($sql);
  $row = mysql_fetch_row($result);
 
  if($row['parent'] != null)
    return getTopLevelParent($row['parent']);
  else
    return $row['parent'];
}
 

Good point. I will remember that. The only thing is, the top-level parent will be a group name and not a person's name, so I will need a group column, and I think it will need updating. Unless everytime I want to see a user's group it searches to find the top level parent and puts his group there?

Correct me if I'm wrong but wouldn't it be quicker to update every person in the tree if someone changes (shouldn't happen that often) so when I recall a user it is in their own row? Hope I made sense.

[Atli]

Good point. I will remember that. The only thing is, the top-level parent will be a group name and not a person's name, so I will need a group column, and I think it will need updating. Unless everytime I want to see a user's group it searches to find the top level parent and puts his group there?

Correct me if I'm wrong but wouldn't it be quicker to update every person in the tree if someone changes (shouldn't happen that often) so when I recall a user it is in their own row? Hope I made sense.

In that case your original concept isn't a bad one.
Adding a group name to each person would be an awful wast of space tho.

You should consider creating a different table for the groups, where you list all group names. Then you can simply link every person to it's group using the ID of the group as a foreign key in the person table.

If for some reason you change the group for the leader, and you want all his minions to follow him, the you would have to change the group ID for them all which would require a recursive update function... somewhat like you posted in your original post.

Personally, I wouldn't use this sort of hierarchy to control user groups.
I would create some sort of a role system, somewhat like the "Admin->Mod->Member" concept of a forum.
Then each person could be linked to multiple groups and even given different roles for each group.

[TheServant]

In that case your original concept isn't a bad one.
Adding a group name to each person would be an awful wast of space tho.

You should consider creating a different table for the groups, where you list all group names. Then you can simply link every person to it's group using the ID of the group as a foreign key in the person table.

If for some reason you change the group for the leader, and you want all his minions to follow him, the you would have to change the group ID for them all which would require a recursive update function... somewhat like you posted in your original post.

Personally, I wouldn't use this sort of hierarchy to control user groups.
I would create some sort of a role system, somewhat like the "Admin->Mod->Member" concept of a forum.
Then each person could be linked to multiple groups and even given different roles for each group.

Sorry, I was simplifying the problem, but yes, it will be a group ID column, as I will have a group table with it's own group variables. And for interest sake, its going to be so that users can create their own groups and can't join any others. Also the hierarchy wil be infinite so the tree width and length has no limit.

Along with foreach loops, I have limited experience using forign keys. Any good tutes? Is it done when creating the table? Thanks for your help, always know I will find answers here!

[pbmods]

And with teh loop, should it be:
$result = mysql_query('...');
foreach( $row = mysql_fetch_object($result) )
?

It should be while( $row = mysql_fetch_object($result) ). foreach is only used to loop through an array.

[TheServant]

It should be while( $row = mysql_fetch_object($result) ). foreach is only used to loop through an array.

Point taken. Cheers mate.