Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

Inserting more than one entry into a MySql DB

by Kiko (Scribe)
on Feb 01, 2002 at 19:03 UTC ( [id://142739]=perlquestion: print w/replies, xml ) Need Help??

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); }

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);
      Hi,
      I retrieve the icodes as such:
      @icodes = split(/,/, $q->param('icodes'));
      Are you saying that i should just leave it alone?
      Thanks
      -Kiko
        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.

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
      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
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";
    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');
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);

    I mistrust all systematizers and avoid them. The will to a system shows a lack of integrity -- F. Nietzsche

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://142739]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others examining the Monastery: (5)
As of 2024-03-28 23:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found