DBI and Inserting an Array

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

Hey guys,

I have been looking for a good example for what I am trying to do, but I am finding it harder than I expected. I have a script that builds an array of words using regex.

What I would like to do is push this array into a MySQL table. The script iterates through a file, line by line, and finds several things per line. e.g.

Each line would be something like this:

Selling a boat for $100. Please call John Smith at 555-1234. My address is 123 Spring Lane, Springfield MO 12345.

So, lets say you have hundreds of lines like this and you want:

@array = (Smith, John, boat, $100, 123 Spring Lane, Springfield, MO, 12345);

I reinitialize the array for each line, so I have an array for the info I want for each "ad". I want to be able to push this into an "ads" table in a MySQL table, but I can't find a good example of specifically doing it with an array.. I got all the way to the VALUES part, and couldn't find a good recipe for the syntax. I was hoping it might be like this:

$query = INSTERT INTO ads ("lname,fname,item,price,street,city,state,z
+ip) VALUES (@array);
[download]

Thanks for your help in advance.

Comment on DBI and Inserting an Array Download Code

Replies are listed 'Best First'.
Re: DBI and Inserting an Array by The Mad Hatter (Priest) on Mar 12, 2004 at 00:24 UTC
Kinda close (and that example you gave would throw a bunch of syntax errors, but I'm going to assume you know how to use the DBI; if you don't, read the doc and many tutorials here on this site). Here's how I would do it: `my $dbh = DBI->connect( ... ); # fill in the arguments you need my $sth = $dbh->prepare(<<'SQL'); INSERT INTO ads (lname,fname,item,price,street,city,state,zip) VALUES (?,?,?,?,?,?,?,?) SQL while (my $line = <SOME_FILEHANDLE>) { # ... parse $line to get data in @array... you've done this # assuming @array contains 8 values, this inserts the data $sth->execute(@array); }` [download] You'd want to check for errors on the insert though if you don't have RaiseError or an error handler in place.	[reply] [d/l]
Re: DBI and Inserting an Array by krujos (Curate) on Mar 12, 2004 at 00:28 UTC
I was able to do it using the following. `my $sth = $dbh->prepare("INSERT INTO $table ($cols) VALUES (?,?,?)") or die "$dbh->errstr"; my @array=('one', 'two', 'thee'); $sth->execute(@array) or die "$dbh->errstr : $table";` [download] You should be able to plug your code in pretty easily.	[reply] [d/l]
Re: Re: DBI and Inserting an Array by carric (Beadle) on Mar 12, 2004 at 04:55 UTC
Excellent. Thanks guys. That did the trick!! =)	[reply]


Welcome to the Monastery
	PerlMonks