That is most definitely what is happening. For exactly that reason I find
it to be a useful practice to limit Statement Handles to one query per
handle. It's sometimes handy to store them as a hash.
You are also preparing statements inside your fetch loop which is re-work
that is not necessary. If you move all of your prepares outside the loop,
you'll find that performance will improve dramaticly, assuming the first
query is returning more than just a couple rows.
my %sql;
$sql{orders} = 'select cust_id, prod_id, qty, amt, order_no, order_dt from
orders';
$sql{customers} = 'select * from customer_filters where cust_id = ?';
$sql{products} = 'select * from product_filters where prod_id = ?';
my %sth;
$sth{orders} = $dbh->prepare($sql{orders});
$sth{customers} = $dbh->prepare($sql{customers});
$sth{products} = $dbh->prepare($sql{products});
$sth{orders}->execute();
while (my ($cust_id, $prod_id, $qty, $amt, $order_no, $order_dt) =
$sth{orders}->fetchrow_array()){
$sth{customers}->execute($cust_id);
do stuff;
$sth{products}->execute($prod_id);
do stuff;
etc..}
... or something like that.
This is top - of - head code.....expect typos.
Jeff Seger
Fairchild Semiconductor
[EMAIL PROTECTED]
Ronald J Kimball <[EMAIL PROTECTED]>
07/30/2003 02:18 PM
To: Adam Peterson <[EMAIL PROTECTED]>
cc: [EMAIL PROTECTED]
Subject: Re: Help With inner queries
On Wed, Jul 30, 2003 at 10:40:44AM -0700, Adam Peterson wrote:
> sorry should have clarified...
>
> the code works when there is only one row returned in the outermost
query.
>
I think JT is right, you've overwritten your original statement handle in
$sth. Either use different variable names or a lexically scoped $sth
inside the loop.
Ronald
