How can I insert parts of an array into MySQL?

Question

I have an array in Perl like the following (only much bigger). My array is called @sqlInsert

Element1 A B C

Element2 A B C

Element3 A B C

I want to fill a MySQL table with the information in this data. So really, I want to have one row going:

 for ($count=0; $count<@arrayInsert; $count++) {
      INSERT INTO table values ("sqlInsert[$i]","sqlInsert[$i+1]","sqlInsert[$i+2]","sqlInsert[$i+3]")
 }

Any pointers?

The button with the binary on it means "format as code".

Ether
– Ether

2010-11-04 23:09:48 +00:00
Commented Nov 4, 2010 at 23:09 — Ether
– Ether, Commented Nov 4, 2010 at 23:09

brian d foy · Accepted Answer · 2012-04-05 02:10:55Z

3

Use DBI placeholders and...

...an array slice:

 my $sth = $dbh->prepare( 'INSERT INTO table VALUES ( ?, ?, ?, ? )' );

 $sth->execute( @sqlInsert[ $n .. $m ] );

...or a splice:

 while( @sqlInsert ) {
      $sth->execute( splice @sqlInsert, 0, $length, () );
      }

It might be easier to create a data structure, such as an array of arrays, that already groups the elements for each structure:

 foreach my $ref ( @sqlInsert ) {
      $sth->execute( @$ref );
      }

edited Apr 5, 2012 at 2:10

answered Nov 4, 2010 at 22:12

brian d foy

134k31 gold badges214 silver badges613 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

poutine Over a year ago

Thanks for the reply, Brian ... I went with your first solution. However, here is the error, I get: >Use of uninitialized value in range (or flop) at sqlInsert.pl line 56. >Use of uninitialized value in range (or flop) at sqlInsert.pl line 56. >Argument "" isn't numeric in array slice at sqlInsert.pl line 56. >DBD::mysql::st execute failed: called with 1 bind variables when 4 are needed at sqlInsert.pl line 56. Here is my line 56: > $sth->execute( @td[ $n .. $m ] );

brian d foy Over a year ago

I ordered a crystal ball from Amazon. When it gets here, I'll look at your code. :)

poutine Over a year ago

Fair enough buddy, I probably deserved that: >#Auth >$platform = "mysql"; >$database = "db"; >$host = "localhost"; >$user = "root"; >#Connect to DB >$DBIconnect = DBI->connect($dsn, $user); >my $sth = $DBIconnect->prepare( 'INSERT INTO table VALUES ( ?, ?, ?, ? )' ); >$sth->execute( @sqlInsert[ $n .. $m ] ); ------ Can you let me know what the $n ... $m does?

brian d foy Over a year ago

You might want to start with Learning Perl if you need to pick up the basics. I'm not going to try to read code in comments. Update your question with code.

Ether Over a year ago

@poutine: twice in one day I've pointed you to the documentation: please look up .. in perldoc perlop. If you have never done so before, type perldoc perl at the command line and marvel at all the documentation that is ready at your fingertips.

|

Collectives™ on Stack Overflow

How can I insert parts of an array into MySQL?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related