Inserting more than one entry into a MySql DB

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

Hi All,

This is a small portion of a script i'm working on. All it's supposed to do is insert several entries into a MySql database. I have the following array @icodes="CEN01-SJU1-022102A CEN02-MEX1-022702P CTE01-SJU1-022202A CTE02-MEX1-022802P"; When i run the small part of the script all it does is insert the first entry in the array. Everything in the insert statement should remain the same for each entry, except for the $t which is supposed to change for every entry in the array.

Any help will be greatly appreciated.

-Kiko

my ($order_num) = sprintf "%lx",time; # Generates the order number
    
# Adds ON (Order Number) infront of the generated number and makes eve
+rything upper case
$order_num = uc("ON$order_num");    

my ($reg) = $dbh->prepare('INSERT INTO registrations (order_num, custo
+mer_id, payment_type, icode) VALUES (?, ?, ?, ?)');

foreach my $t (@icodes) {

   $reg->execute($order_num, $customer_id, 'CREDIT', $t);
}
[download]

Comment on Inserting more than one entry into a MySql DB Download Code

Replies are listed 'Best First'.
Re: Inserting more than one entry into a MySql DB by dws (Chancellor) on Feb 01, 2002 at 19:14 UTC
Show us the line that sets up `@icodes`. If, as you say, it is `@icodes="CEN01-SJU1-022102A CEN02-MEX1-022702P CTE01-SJU1-022202A CTE02-MEX1-022802P";` then it only contains a single element. To make that an array, do something like `@icodes=qw(CEN01-SJU1-022102A CEN02-MEX1-022702P CTE01-SJU1-022202A CTE02-MEX1-022802P);`	[reply] [d/l] [select]
Re: Re: Inserting more than one entry into a MySql DB by Kiko (Scribe) on Feb 01, 2002 at 19:45 UTC
Hi, I retrieve the icodes as such: @icodes = split(/,/, $q->param('icodes')); Are you saying that i should just leave it alone? Thanks -Kiko	[reply]
Re: Re: Re: Inserting more than one entry into a MySql DB by dws (Chancellor) on Feb 01, 2002 at 21:27 UTC
I retrieve the icodes as such: @icodes = split(/,/, $q->param('icodes')); Are you saying that i should just leave it alone? That should work. I think screamineagle nailed this one. You might be trying to reuse a primary key. Adding error checking after the `execute()` might reveal this as a problem.	[reply] [d/l]
Re: Inserting more than one entry into a MySql DB by screamingeagle (Curate) on Feb 01, 2002 at 19:15 UTC
I see that the Order number is staying the same for each iteration... if the order num column is the primary key in the table, then it is logical that only the first insert will work, and all the others will fail, because the primary key will have a UNIQUE constraint by default which will prevent it from having duplicate values. You need to generate a new value for the primary key for each insert statement	[reply]
Re: Re: Inserting more than one entry into a MySql DB by Kiko (Scribe) on Feb 01, 2002 at 19:48 UTC
Hi, You were right, i changed the primary field to something else and it worked. I was focusing on the script and didn't realize that the problem was in the way i set up the database. Thanks for your help -Kiko	[reply]
Re: Inserting more than one entry into a MySql DB by chromatic (Archbishop) on Feb 01, 2002 at 19:16 UTC
If I read you correctly, you've declared a single element array: `@icodes="CEN01-SJU1-022102A CEN02-MEX1-022702P CTE01-SJU1-022202A CTE02-MEX1-022802P";` [download] In that case, Perl is happily looping through each element of the array, all one entries. How about: `@icodes = ('CEN01-SJU1-022102A', 'CEN02-MEX1-022702P', 'CTE01-SJU1-022202A', 'CTE02-MEX1-022802P');` [download]	[reply] [d/l] [select]
Re: Inserting more than one entry into a MySql DB by arturo (Vicar) on Feb 01, 2002 at 19:19 UTC
Going from what you say in the main text, you have set `@icodes` to a singleton array (it containst that one string, with all the icodes separated by a space). perhaps what you want instead is `@icodes = qw(CEN01-SJU1-022102A CEN02-MEX1-022702P CTE01-SJU1-022202A +CTE02-MEX1-022802P);` [download] I mistrust all systematizers and avoid them. The will to a system shows a lack of integrity -- F. Nietzsche	[reply] [d/l] [select]