Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

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

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

    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.


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?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://95043]
Approved by root
[marto]: This won't take till Summer to complete I hope ;)
[Discipulus]: i hope too, vacuum cleaner permitting, 2-3 afternoon to build. Or is the 1:1 serie? ;=)
[Tanktalus]: Sure, no one is active for hours and hours in the cb, so I go to upgrade the db, and then someone is active. Sheesh :)
[Discipulus]: anyway poppins probably died with the last night cold. But is not normal to see them in dec. they must pop in April
NodeReaper eyes the thorns in the side
Discipulus : the party puller!
[Discipulus]: I was trying to solve this but i'm not. my regex-fu is stuck at primary school

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (6)
As of 2017-12-18 21:35 GMT
Find Nodes?
    Voting Booth?
    What programming language do you hate the most?

    Results (498 votes). Check out past polls.