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

@lists = check_id($id);

print "In: @lists";

sub check_id {
 $ids = shift;

 $sthm = $dbh->prepare("SELECT name,parent FROM category WHERE id = '$
+ids'");
 $sthm->execute or die $dbh->errstr;

 ($name,$parent) = $sthm->fetchrow_array;
 push(@info, $name);

 if($parent ne "0") {
  $line = check_id($parent);
      push(@info, $line);
 }

 return(@info);
}

[download]
Say my $id was 11, then it would return. 
ff xx aa other 4 5 6

[download]
I have no idea why those numbers are there, there not suppost to be, heres what my table looks like in php my admin.
id name parent description view_cats_prod
Edit Delete 3 snowmobiles 0    
Edit Delete 4 other 0    
Edit Delete 5 parts 3    
Edit Delete 8 a 0 a a
Edit Delete 9 aa 4 aa no
Edit Delete 10 xx 9 d no
Edit Delete 11 ff 10 ss no
Edit Delete 12 dd 0 ff no
Edit Delete 13 dd 0 ff no
Edit Delete 17 a 12 x yes
Edit Delete 15 a 0 a yes

Replies are listed 'Best First'.
Re: Looping through MySQL
by PodMaster (Abbot) on Jul 19, 2002 at 22:00 UTC
    Hmm, why aren't you using placeholders?

    ____________________________________________________
    ** The Third rule of perl club is a statement of fact: pod is sexy.

[reply]
      Whats a placeholder
[reply]
        I suggest you take a look at your local DBI documentation, as well as How to RTFM.

        What's the point of using 'prepare' if you're not using placeholders?

        ____________________________________________________
        ** The Third rule of perl club is a statement of fact: pod is sexy.

[reply]
[reply]
Re: Looping through MySQL
by Aristotle (Chancellor) on Jul 19, 2002 at 22:13 UTC

    Using strict and warnings would have uncovered that the problem is your recursive calls to the function pushing values onto the global @info you return at the bottom.

    Update: Oops.. upon further look, the problem is in $line = check_id($parent); which does not work as it looks because you are receiving an array. In scalar context, an array evaluates to the number of its elements, which is what $line then contains, and subsequently gets pushed onto the array. You wanted to say ($line) = check_id($parent); which forces list context and captures the first element of the list in the variable. However you will then get duplicate elements because your recursive calls, as I already said, are modifying the global @info.

    Makeshifts last the longest.

[reply]
[d/l]
[select]
Re: Looping through MySQL
by dws (Chancellor) on Jul 19, 2002 at 22:24 UTC
    You can use placeholders and pull the prepare() out of the loop, but what's tripping you up is the way you're (not) lexically scoping your variables when you're doing recursion.

    You're also treating @info as both a global and a return value (from each level of the recursion). I think that's not what you indend.

    This is pretty easy to debug. Each time you push a value onto @info, print $name, $parent, and the value that you're pushing. Then ponder carefully just how it is that those values are what they are. For extra learning potential, do this on paper before you run the script, and compare your paper results with what gets printed.

[reply]
Re: Looping through MySQL
by Theseus (Pilgrim) on Jul 20, 2002 at 00:31 UTC
    Call me crazy, but if I were you I would be using selectrow_array or selectrow_hashref for your data retrieval. For instance: 
    $href = $dbh->selectrow_hashref("SELECT name,parent FROM category WHER
+E id = '$ids'") or die $dbh->errstr;

 push(@info, $href{'name'});

    [download]
    I believe that would be better suited to your needs.
[reply]
[d/l]
[select]
Re: Looping through MySQL
by flocto (Pilgrim) on Jul 20, 2002 at 13:42 UTC

    When doing the same (well, almost) query and again, you do want to reuse the statement handle for several reasons. Take a look at the DBI documentation if you want to know why. Anyhow, the basic idea would look like this:

    #this is an exsample and will not run right away!
use strict;
use DBI;
use vars qw#$DBH $STH#;

my $dsn = "foo";
my $user = "bar";
my $passwd = "baz";

$DBH = DBI->connect ($dsn, $user, $passwd) or die DBI->errstr ();
$STH = $DBH->prepare ("SELECT name, parent FROM table WHERE id = ?") o
+r die $DBH->errstr ();

my $id = int (rand (100));
my @list = check_id ($id);

print STDOUT join (', ', @list) . "\n";

sub check_id
{
    my $id = shift;

    $STH->execute ($id) or die $STH->errstr ();
    my ($name, $parent) = $STH->fetchrow_array ();
    $STH->finish ();

    # !!! local scope !!!
    my @list = ($name);

    if ($parent)
    {
        push (@list, check_id ($parent);
    }

    return (@list);
}

    [download]

    This code should do the magic.. Please make sure that you realize that @list in the subroutine is NOT identical with @list in the main program. Also take a look at the statement handle which is initialized only once (using the placeholder "?").

    Regards,
    -octo

[reply]
[d/l]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom