dbi newbie question

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

Hi, I'm somewhat new at perl and just started playing with DBI this morning. I'm having a problem when looping through a recordset and executing a SELECT within the while loop which depends on a value from the first. It appears that on every iteration rows are being appended to $sth, but I want to empty $sth on every iteration and get a fresh recordset.

ex: first iteration returns:
1 foo
2 bar

next iteration is appending to first:
1 foo
2 bar
3 musty
4 tshirt

the second iteration should just return:
3 musty
4 tshirt

I've included the while loop below, thanks for any help.

while ( my ($intMemberID,$txtUsername,$txtPassword,$txtName,$txtEmail,
+$txtBio) = $rsMembers->fetchrow_array) {
    my $sth = $dbh->prepare( "SELECT intSiteID,txtURL,txtTitle,txtDesc
+ription FROM Sites WHERE intMemberID = ? ORDER BY txtTitle" );
    $sth->execute($intMemberID);
    while ( my ($intSiteID,$txtURL,$txtTitle,$txtDescription) = $sth -
+>fetchrow_array) {
        $websites.= load_template("member_site_index.tmpl", {sid => $i
+ntSiteID, url => $txtURL, title => $txtTitle, description => $txtDesc
+ription});        
    }
    $sth ->finish;
}
[download]

Comment on dbi newbie question Download Code

Replies are listed 'Best First'.
Re: dbi newbie question by agoth (Chaplain) on Jul 09, 2001 at 20:51 UTC
I dont think the problem is with DBI, but due to the fact you are appending all your results onto the end of $websites?? If for each iteration with `$sth->execute($intMemberID);` [download] you put the result set in a different variable and did something different with it, you may be able to solve your display problem? If thats not the right interpretation, just say???	[reply] [d/l]
Re: dbi newbie question by TheoPetersen (Priest) on Jul 09, 2001 at 20:52 UTC
DBI wouldn't do what you describe, unless you have duplicate rows in your tables or something. Perhaps the problem is that you are always appending to $websites and not resetting it? Try putting `$websites = '';` [download] after the execute call.	[reply] [d/l]
Re: Re: dbi newbie question by PrakashK (Pilgrim) on Jul 09, 2001 at 21:07 UTC
Or, make `$websites` a lexically scoped (my) variable, in your outer while loop (before you start the inner while loop). That will ensure that the variable is newly created for every iteration. /prakash	[reply] [d/l]
Re: dbi newbie question by dsb (Chaplain) on Jul 09, 2001 at 21:23 UTC
Just a side note, since your question has already been answered. You are preparing $sth inside your loop. For the sake of efficiency you could define and prepare $sth before the 1st loop starts. As it is now, you are preparing it everytime you go through the loop when it only needs to be prepared once. So your code would read: my $sth = $dbh->prepare( "SELECT ntSiteID,txtURL,txtTitle,txtDescripti +on FROM Sites WHERE intMemberID = ? ORDER BY txtTitle" ); while ( my ($intMemberID,$txtUsername,$txtPassword,$txtName,$txtEmail, +$txtBio) = $rsMembers->fetchrow_array) { $sth->execute($intMemberID); while ( my ($intSiteID,$txtURL,$txtTitle,$txtDescription) = $sth-> +etchrow_array) { $websites.= load_template("member_site_index.tmpl", {sid => $i +ntSiteID, url => $txtURL, title => $txtTitle, description => $txtDesc +ription}); } $sth ->finish; } [download] Amel - f.k.a. - kel	[reply] [d/l]
Re: dbi newbie question by RickySilk (Initiate) on Jul 10, 2001 at 04:03 UTC
Thank you everyone, it was so obvious, yet I didn't see it. I wasn't resetting $websites to "". Sorry.	[reply]