Possible loop problems in database query

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

Make this the fourth SOPW on this problem, hoping this will be the last :) In short, I have a MySQL database that has infinite levels of categories/subcategories. It's set up like..

id   parentid   catname   catdesk
[download]

Using the parentid to locate the next-up category to which it belongs. If it's 0, it's a parent category. I wrote the hack below but it seemingly goes through a loop and reprints the subcategories many times.

# print the beginning of the select
print "<select name='categories' SIZE='5'>\n";

# select top-level elements, and specify top level
recurQuery(0,0);

# my super recurse the tree function, takes 2 options being parentid, 
+and current level
sub recurQuery {
        my $parentId=$_[0];
        my $level=$_[1];
        my $data = "SELECT id, parentid, catname from categories WHERE
+ parentid = $parentId";
        my $sth = $dbh->prepare($data);
        $sth->execute() or die $dbh->errstr;

        # loop through results
        while (my @results = $sth->fetchrow_array) {
                
                # print the beginning of the option box
                print"<option name='$results[2]'>";
                
                # loop based on level, for indentation
                for (my $i = 0; $i < $level; $i++) {
                        print "&nbsp;*";
                }
                
                # print end of option box
                print $results[2]."</option>\n";
                
                # find child elements of this one
                my $data = "SELECT id, parentid, catname from categori
+es WHERE parentid = $results[0]";
                my $sth = $dbh->prepare($data);
                $sth->execute() or $dbh->errstr;
                
                # get number of rows returned
                my $rownumbersub = $sth->rows;
                
                # if rows returned, exec this
                if($rownumbersub > 0) {
                        
                        # loop through children
                        while (my @resultssub = $sth->fetchrow_array) 
+{
                                
                                # re-exec this function with the child
+ ID and new level
                                recurQuery($resultssub[1],$level+1);
                        }
                }
        }
}

# close that thar select box
[download]

The results are as follows. As you can see, the subcategories reprint themselves. They are only in the database ONE time, so that can't be the problem. Can anyone see what might be going on her?

<select name='categories' SIZE='5'>
<option name='insect'>insect</option>

<option name='mammal'>mammal</option>
<option name='Sea Life'>&nbsp;*Sea Life</option>
<option name='Dolphin'>&nbsp;*&nbsp;*Dolphin</option>
<option name='Seal'>&nbsp;*&nbsp;*Seal</option>
<option name='Dolphin'>&nbsp;*&nbsp;*Dolphin</option>
<option name='Seal'>&nbsp;*&nbsp;*Seal</option>
<option name='Cats'>&nbsp;*Cats</option>

<option name='Dogs'>&nbsp;*Dogs</option>
<option name='Sea Life'>&nbsp;*Sea Life</option>
<option name='Dolphin'>&nbsp;*&nbsp;*Dolphin</option>
<option name='Seal'>&nbsp;*&nbsp;*Seal</option>
<option name='Dolphin'>&nbsp;*&nbsp;*Dolphin</option>
<option name='Seal'>&nbsp;*&nbsp;*Seal</option>
<option name='Cats'>&nbsp;*Cats</option>

<option name='Dogs'>&nbsp;*Dogs</option>
<option name='Sea Life'>&nbsp;*Sea Life</option>
<option name='Dolphin'>&nbsp;*&nbsp;*Dolphin</option>
<option name='Seal'>&nbsp;*&nbsp;*Seal</option>
<option name='Dolphin'>&nbsp;*&nbsp;*Dolphin</option>
<option name='Seal'>&nbsp;*&nbsp;*Seal</option>
<option name='Cats'>&nbsp;*Cats</option>

<option name='Dogs'>&nbsp;*Dogs</option>
<option name='bird'>bird</option>
</select>
[download]

Comment on Possible loop problems in database query Select or Download Code

Replies are listed 'Best First'.
Re: Possible loop problems in database query by davidrw (Prior) on Apr 04, 2007 at 15:56 UTC
don't see the issue yet, but here's a crack at refactoring to simplify it some, which hopefully will lead you in the right direction.. `recurQuery(0,0); my $cat_sth = $dbh->prepare("SELECT id, parentid, catname from categor +ies WHERE parentid = ?"); sub recurQuery { my ($parentId, $level) = @_; $cat_sth->execute($parentId) or die $dbh->errstr; while (my $data = $cat_sth->fetchrow_hashref) { printf qq{ <option name="%s">%s%s</option>\n }, $data->{catname}, ' *' x $level, $data->{catname}, ; recurQuery( $data->{id}, $level+1 ); } }` [download] Some notes: prepare the statement once use placeholders the query only needs to be in the function once -- recursion does the rest; don't need to pre-test for rows .. the while loop just won't happen in the next level if there aren't any more children the `x` operator can replace the for loop for the indenting printf for cleaner output (also consider separating logic/presentation even further by having the recurQuery return an array, and loop over that to display later) fetch hashref's instead of rows -- much easier to read a key name than just an array index You should put in some protection for infinite recursion, too.	[reply] [d/l] [select]
Re: Possible loop problems in database query by pgor (Beadle) on Apr 04, 2007 at 16:56 UTC
The intent of your recursive function is "find every immediate child of the specified parentId, print it as an option element, immediately followed by all of its children, etc." There are two issues with your code breaking this: Your method is handling two levels per invocation, instead of just one. The recursive call to your function is passing the current element's parentid, not the id. (Remember, "immediate child of the specified/current element.") This should help: # my super recurse the tree function, takes 2 options being parentid, +and current level sub recurQuery { my ($myParentId,$level) = @_; my $data = "SELECT id, parentid, catname from categories WHERE par +entid = ?"; my $sth = $dbh->prepare($data); $sth->execute($myParentId) or die $dbh->errstr; my $indent = ' *' x $level; # loop through results while (my ($id,$parentid,$catname) = $sth->fetchrow_array()) { # print the beginning of the option box print"<option name='$catname'>$indent$catname</option>\n"; recurQuery( $id, $level + 1 ); } } [download] pg	[reply] [d/l]