Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

dbi newbie question

by RickySilk (Initiate)
on Jul 09, 2001 at 20:45 UTC ( #95043=perlquestion: print w/ replies, xml ) Need Help??
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; }

Comment on dbi newbie question
Download Code
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);
    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???

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 = '';
    after the execute call.
      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

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

    Amel - f.k.a. - kel

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.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (5)
As of 2014-12-29 08:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (185 votes), past polls