Hierarchical Tree Traversal

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Could anyone advise as to why this script doesn't work:


#$dbh->do("create table terms (id INTEGER not NULL,
#  term TEXT,tree TEXT, parent_id INTEGER,left_id 
#  int,right_id int,PRIMARY KEY (id))"
#                            
#    );
                
#$dbh->do(qq| INSERT INTO terms VALUES (1,"ANATOMY BODY REGIONS","A01"
+,"1","1","8")|);
#$dbh->do(qq| INSERT INTO terms VALUES (2,"ABDOMEN","A01.047","1","2",
+"")|);
#$dbh->do(qq| INSERT INTO terms VALUES (3,"AXILA","A01.133","1","","")
+|);
#$dbh->do(qq| INSERT INTO terms VALUES (4,"EXTREMITIES","A01.378","1",
+"","")|);
#$dbh->do(qq| INSERT INTO terms VALUES (5,"ARM","A01.378.209","4","","
+")|);
#$dbh->do(qq| INSERT INTO terms VALUES (6,"ELBOW","A01.378.209.235","5
+","","")|);
#$dbh->do(qq| INSERT INTO terms VALUES (7,"HAND","A01.378.209.455","5"
+,"","")|);
#$dbh->do(qq| INSERT INTO terms VALUES (8,"FINGERS","A01.378.209.455.4
+30","7","","")|);

my $result= $dbh->prepare("SELECT id FROM terms WHERE parent_id = ?");
my $left = $dbh->prepare("UPDATE terms SET left_id=? WHERE id = ?");
my $right= $dbh->prepare("UPDATE terms SET left_id=? WHERE id = ?");


my $root =1;
my $counter = 1;
rebuild_tree($root);

sub rebuild_tree{
my $idy = shift;


$left->execute($ctr++,$idy);
$result->execute($idy);
  while (my $idy = $result->fetchrow_hashref()) {
  $idy = $i->{id};
  rebuild_tree($idy);
  }
$right->execute($ctr++,$idy);

}#end of sub
[download]

The error message I get is:


DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at s
+et_depth1.
pl line 67.
DBD::mysql::st fetchrow_hashref failed: fetch() without execute() at s
+et_depth1.
pl line 67.
[download]

When I put all the prepare statements in the rebuild_tree sub, the results are not right. Anything wrong with the recursive method or is it a problem with my scoping? Any help would be much appreciated. I am trying to build a nested set database to access some hierarchical data. I am using MySQL as my back-end database.

Comment on Hierarchical Tree Traversal Select or Download Code

Replies are listed 'Best First'.
Re: Hierarchical Tree Traversal by jZed (Prior) on Jul 15, 2004 at 18:04 UTC
Do you have RaiseError on? `while (my $idy = $result->fetchrow_hashref()) { $idy = $i->{id};` [download] What is $i? Why is $idy defined twice? Is there a reason you are trying to reinvent this wheel instead of using DBIx::Tree or DBIx::Tree::NestedSet?	[reply] [d/l]
Re^2: Hierarchical Tree Traversal by Raad (Acolyte) on Jul 15, 2004 at 19:31 UTC
$idy is essentially an alias for the id generated from the result sql statement. I shift it to replace $root so that it becomes the iterator for the id comparisons. I've used it twice for clarity and I think I was having a problem using $id in my script because it's also used as a field in my data. $i is just a reference of ... oops I see that I should have used $i with my while rather than $idy. I've spent a day familiarizing myself with DBIx::Tree::NestedSet but I can't figure out how to traverse the tree programmatically. It looks like it's not designed to populate the left and right ids!	[reply]
Re^3: Hierarchical Tree Traversal by Hero Zzyzzx (Curate) on Jul 16, 2004 at 00:23 UTC
It creates the left and right values automatically (I'm the author of DBIx::Tree::NestedSet) when you invoke any method that deletes/moves/adds nodes. What use would the module be if it didn't? Did you even try the code I posted in the other thread you created? With a nested set you don't need a parent ID: the hierarchy is created via left and right. Managing a parent id is redundant. Traversing the tree is right in the docs. -Any sufficiently advanced technology is indistinguishable from doubletalk. My Biz	[reply]
Re^4: Hierarchical Tree Traversal by Anonymous Monk on Jul 16, 2004 at 01:53 UTC
Re^3: Hierarchical Tree Traversal by Raad (Acolyte) on Jul 15, 2004 at 19:50 UTC
Thanks for directing me in the right direction. The problem was indeed the $i reference. Also I had a mistake in my right statement. The correct algorithm to generate right and left ids for a hierarchical tree should be: `my $root =1; my $counter = 1; rebuild_tree($root); sub rebuild_tree{ my $idy = shift; my $result= $dbh->prepare("SELECT id FROM terms WHERE parent_id = ?"); my $left = $dbh->prepare("UPDATE terms SET left_id=? WHERE id = ?"); my $right= $dbh->prepare("UPDATE terms SET right_id=? WHERE id = ?"); $left->execute($ctr++,$idy); $result->execute($idy); while (my $i = $result->fetchrow_hashref()) { rebuild_tree($i->{id}); } $right->execute($ctr++,$idy); }#end of sub` [download] The only way I could get it to work is by inserting the prepare handles in the sub.	[reply] [d/l]
Re^4: Hierarchical Tree Traversal by eric256 (Parson) on Jul 15, 2004 at 20:05 UTC