Updating a tree structure

Heya, TheServant.

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

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:

Quote: Originally Posted by 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): Code: ( text ) $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.

Quote: Originally Posted by 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: Code: ( text ) 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.

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

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

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

Quote: Originally Posted by pbmods It should be while( $row = mysql_fetch_object($result) ). foreach is only used to loop through an array.

Point taken. Cheers mate.