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.

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]
      Excellent. Thanks guys. That did the trick!! =)
[reply]

Back to Seekers of Perl Wisdom