Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Possible loop problems in database query

by coldfingertips (Pilgrim)
on Apr 04, 2007 at 15:27 UTC ( #608298=perlquestion: print w/replies, xml ) Need Help??

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

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}, '&nbsp;*' x $level, $data->{catname}, ; recurQuery( $data->{id}, $level+1 ); } }
    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.
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:
    1. Your method is handling two levels per invocation, instead of just one.
    2. 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 = '&nbsp;*' 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 ); } }
    pg

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (2)
As of 2021-12-02 18:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    R or B?



    Results (23 votes). Check out past polls.

    Notices?