Re: Efficient select/insert

[Jonathan Mangin]

----- Original Message ----- 
From: "Jonathan Mangin" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, April 26, 2005 11:26 AM
Subject: Efficient select/insert


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();
  my @row = $sth->fetchrow_array;
  $sql = "insert table2 (col1, col2, col3, col4)
          values (NULL, ?, ?, ?)";
  $sth = $dbh->prepare($sql);
  $sth->execute($row[0], $row[1], $row[2]) or die $sth->errstr();
}
Is this efficient db interaction, or is there a better way?
This is 3.23 but can upgrade if necessary.
Thanks,
Jon

Further...
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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]