Perl: Breaking out of foreach loop when last array element is encountered

Question

Perl noob here. I have a small script (see below) that I'm using to build a MySQL INSERT statement.

use strict;

my @records = qw/Record1 Record2 Record3/;

my $insert = "
INSERT INTO table
VALUES
 ";

foreach my $record (@records) {
        $insert .= "('" . $record . "'),\n ";
}

print "$insert\n";

Current Output

INSERT INTO table
VALUES
 ('Record1'),
 ('Record2'),
 ('Record3'),

I need to know how to break at the last element of the @records array and append a ; instead of ,

Desired Output

INSERT INTO table
VALUES
 ('Record1'),
 ('Record2'),
 ('Record3');

Answer 1

You can do that with map and join.

my @records = qw/Record1 Record2 Record3/;

my $insert = "
INSERT INTO table
VALUES
 ";

$insert .= join ',', map { '(' . $dbh->quote($_) . ')' } @records;
$insert .= ';'; # this line is not needed

The quote method of $dbh is better than just putting the stuff into quotes because it handles bad stuff for you. The map is not much different from a foreach loop, and the join will take care of putting the commas in between the elements, and not the last one.

Answer 2

On a related matter, I always try to avoid putting data and sql statements on the same line, thus minimize the risk of sql injection. In perl you have a prepare/execute mechanism available for this:

my @records = qw/Record1 Record2 Record3/;
$sth = $dbh->prepare("INSERT INTO table VALUES ?");

foreach my $record (@records) {
      $sth->execute($record);   
}

http://bobby-tables.com/perl.html