Inserting 2 arrays into 2 field in MySQL

poolboi has asked for the wisdom of the Perl Monks concerning the following question:

hi guys, but i'm using Perl DBI to do manupilations now in MySQL
i've got problem trying to input 2 arrays of data into 2 fields at the same time
say for example now i got:

@array1 = [ 1 ,2 , 3];
@array2 = [4, 5, 6];
[download]

and i got say 2 fields User and Time
i tried using this code

foreach $user(@array1){
$sth -> $dbh ->perpare("INSERT INTO `trial` (User) VALUES ('$user')");
$sth->execute();
 
foreach $time(@array2){
$sth -> $dbh ->perpare("INSERT INTO `trial` (Time) VALUES ('$time')");
$sth->execute();
[download]

i got the results like:

User    Time
1          NULL
2          NULL
3          NULL
NULL    4
NULL    5
NULL    6
[download]

how should i change or input my code so i can get this in the database

User      Time
1           4
2           5
3           6
[download]

Comment on Inserting 2 arrays into 2 field in MySQL Select or Download Code

Replies are listed 'Best First'.
Re: Inserting 2 arrays into 2 field in MySQL by ikegami (Patriarch) on May 15, 2008 at 04:08 UTC
my $sth = $dbh->prepare(" INSERT INTO `trial` ( User, Time ) VALUES ( ?, ? ) "); foreach my $i (0..$#array1) { $sth->execute($array1[$i], $array2[$i]); } [download]	[reply] [d/l]
Re^2: Inserting 2 arrays into 2 field in MySQL by poolboi (Acolyte) on May 15, 2008 at 05:48 UTC
thanks for your help it worked just a question what does `foreach my $i (0..$#array1);` [download] means?	[reply] [d/l]
Re^3: Inserting 2 arrays into 2 field in MySQL by chrism01 (Friar) on May 15, 2008 at 06:04 UTC
0..$#array1 means from zero to last index in array eg http://perldoc.perl.org/perlfaq4.html#How-do-I-process/modify-each-element-of-an-array%3F	[reply]
Re^3: Inserting 2 arrays into 2 field in MySQL by ikegami (Patriarch) on May 15, 2008 at 14:47 UTC
$#array1 returns the last index of the array so that iterates over the indexes of the array. Hopefully, the following example will help you understand: `my @array = qw( a b c ); for my $i (0..$#array) { print("$i: $array[$i]\n"); }` [download]	[reply] [d/l]
Re: Inserting 2 arrays into 2 field in MySQL by pileofrogs (Priest) on May 15, 2008 at 04:36 UTC
The reason your data looks like it does is inserts create new records. Your code would work if the 2nd loop did updates instead of inserts. Ikegami's suggestion is the right way to do it. I just thought you'd like to know why your code did what it's doing.	[reply]