CODEWITHSUNDEEP
mysqlarraysperlinsert
Deepak
Deepak

Reputation: 229

How can I insert data from three Perl arrays into a single MySQL table?

I have 3 different arrays in Perl (namely A, B and C). Now, I have a table in mysql which also has 3 fields.

What I am trying to do is get all the contents of array A in first field of mysql table, contents of array B into second field and so on. I tried doing this using foreach loops but it works fine for the first array but does not insert anything for the second and third array.

The code is used is as below:

foreach my $a (@a) {

    my $sql = "insert into es(a) VALUES(\"$a\")";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die "SQL Error: $DBI::errstr\n";
}


foreach my $b (@b) {

    my $sql = "insert into es(b) VALUES(\"$b\")";
    my $sth = $dbh->prepare($sql);
    $sth->execute or die "SQL Error: $DBI::errstr\n";

}

and similarly for third. The column a of the table gets populated correctly but there is no data for column b and c in the table. What am I doing wrong.

Upvotes: 2

Views: 1970

Answers (1)

simbabque
simbabque

Reputation: 54323

I guess you're not very familiar with relational databases. What you've done is:

  1. iterate over @a
  2. prepare a new statement for each item in @a
  3. add one line per insert with only the a value in it; that means:
    • you create a dataset for each item of @a
    • the columns b and c are NULL.

Now you do that for @b and @c likewise. That is not very efficient.

DBI is designed to help you out here. You should consider the following rules (guidelines):

  • Always use DBI's quote method or better placeholders. That saves you the hassle of addind quotes yourself.
  • If there's a loop and several INSERTs or UPDATEs, always prepare your query outside of the loop and just execute it in the loop.

Let's take a look at your problem then. I assume @a, @b and @c all have the same number of items, and you want one line per index of @a, @b and @c. So if you have this data:

my @a = (1, 2, 3);
my @b = qw(foo bar baz)
my @c = (999, 998, 997);

My bet is that you want it to look like this in the db:

a b   c
1 foo 999
2 bar 998
3 baz 997

We therefore need to combine your three INSERTs into one statement. That can be done by iterating over all of them at once. We can use the each_array function from List::MoreUtils to handle the iteration for us. We'll also add the guidelines from above to the code.

use List::MoreUtils qw(each_array);
my $dbh = DBI->connect(); # connect to db here

# prepare the INSERT statement once
my $sth_insert = $dbh->prepare('INSERT INTO es SET a=?, b=?, c=?')
  or die $dbh->errstr;

# create the array iterator
my $ea = each_array(@a, @b, @c);
# iterate over all three arrays step by step
while ( my ($val_a, $val_b, $val_c) = $ea->() ) {
  $sth_insert->execute($val_a, $val_b, $val_c) or die $dbh->errstr;
}

Upvotes: 6

Related Questions