Re: Efficient select/insert


----- Original Message ----- From: "Eric" <ef@xxxxxxxxxxxxxx>
To: "Jonathan Mangin" <jon.mangin@xxxxxxxxxxx>; <dbi-users@xxxxxxxx>
Sent: Tuesday, April 26, 2005 3:43 PM
Subject: Re: Efficient select/insert


At 01:13 PM 4/26/2005, Jonathan Mangin wrote: 
I would like to select several rows from one table
and insert them into another nearly identical table
using Perl/DBI:
my @array = $q->param();  # HTML checkboxes
foreach my $element (@array) {
  my $sql = "select col2, col3, col4 from table1
             where col1 = ?";
  my $sth = $dbh->prepare($sql);
  $sth->execute($element) or die $sth->errstr();


Do these selects first and push the results into an array of arrays. Then prepare your insert and loop through your array on the execute portion only. Short of multiline inserts that should be your fastest way to do things.


Doesn't this (below) affect my ability to do that?


I thought I could use fetchrow_arrayref and push an array
of arrays. The DBI docs say:

"Note that the same array reference is returned for each fetch,
so don't store the reference and then use it after a later fetch."

Sounds like I can't use that. Now I see execute_for_fetch. Does this
sound like a job for execute_for_fetch?

--J


Lead Programmer
D.M. Contact Management
250.383.8267 ext 229 

.

Relevant Pages

  • Re: [PHP] foreach() using current() strange beahvior
    ... that's expected as foreach moves the internal array pointer, ... When using the internal pointer just by calling current(so not moving ... Unless the array is referenced, foreach operates on a copy of the ...
    (php.general)
  • Re: a question about for and foreach
    ... foreach (@array) { ... why @array are changed after this foreach loop!!! ... The "foreach" loop iterates over a normal list value and sets the ... If any element of LIST is an lvalue, you can modify it by modifying ...
    (perl.beginners)
  • Re: safe to delete elements of array in foreach
    ... I agree with Jon on this one. ... I make it a habit not to delete entries in a foreach() loop. ... build an array of keys I want to delete, and after the loop ends, delete ... I don't know whether an operation like this is guaranteed to work in PHP ...
    (comp.lang.php)
  • Re: [PHP] Foreach
    ... The problem I am having is getting both to update the table in MySQL. ... echo "$t"; ... foreach { ... // foreach expects an array, ...
    (php.general)
  • Re: safe to delete elements of array in foreach
    ... (normally this is not the case but not sure in php) ... I make it a habit not to delete entries in a foreach() loop. ... build an array of keys I want to delete, and after the loop ends, ...
    (comp.lang.php)