Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

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
[Lady_Aleena]: Discipulus, it is at 2 now, but with what I am thinking about, it could go to 3. However, only 1 is needed. The second and third are optional.
[shmem]: straight list or named parameters - that depends on whether (and how many) optional arguments you have
[Discipulus]: if so a plain list is ok, imho
[Lady_Aleena]: shmem, I already have the plugin installed, just not active.
[shmem]: if you have 1 optional argument, place that at the end of the list. If you have more, go for named parameters.
[Lady_Aleena]: 1 manditory, 2 optional.
[Discipulus]: my ($need,$opta,$optb ) = @_; .. if $opta..
[shmem]: all else leads to trouble, even if the third argument depends on the existence of the second. That may become brittle.
[Discipulus]: but if have case like subname(15,undef,3 ) maybe bettere named parameters
[Lady_Aleena]: I don't want to have to do: alpha_menu($hash, undef, $type);

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (12)
As of 2017-04-27 12:19 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (506 votes). Check out past polls.