Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Hierarchical Tree Traversal

by Anonymous Monk
on Jul 15, 2004 at 17:50 UTC ( #374744=perlquestion: print w/replies, xml ) Need Help??

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
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.
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.

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};
    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?

      $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!

        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

        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
        The only way I could get it to work is by inserting the prepare handles in the sub.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://374744]
Approved by sgifford
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (4)
As of 2021-05-16 19:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Perl 7 will be out ...





    Results (152 votes). Check out past polls.

    Notices?