comment on

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]

In reply to dbi newbie question by RickySilk

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


Perl Monk, Perl Meditation
	PerlMonks