Add an item to a table from a tab delimited file with the table fields in the 1st row of the file:

 ## get data ##
 open my $DAT, '<', $getfile or croak "cannot open $getfile";
 my @data = <$DAT>;
 close $DAT or croak 'cannot close data';
 chomp @data;

 ## add data to mySQL table 'table' ##
 my $sql = q~
 INSERT INTO table ( item1, item2)
 VALUES( ?, ? )
 ~;
 my $dbh = WebDB::connect();
     for my $i ( 1 .. $#data ) {
         my ( $item1, $item2 ) = split /\t/xsm, $data[$i];
         my $sth = $dbh->prepare($sql);
         $sth->execute( $item1, $item2 );
     }
 $sth->finish();
 $dbh->disconnect(); 

To update existing items in the table: (same data file as above)

 my $sql = q~
 UPDATE table SET item1 = ?, item2 = ?
 WHERE item1 = ?
 ~;
 my $dbh = WebDB::connect();

 for my $i ( 1 .. $#data ) {
     my ( $item1, $item2 ) = split /\t/xsm, $data[$i];
     my $sth = $dbh->prepare($sql);
     $sth->execute( $item1,  $item2, $item1 );
 }
 $sth->finish();
 $dbh->disconnect();
  

To simply open that table as an array (with selectors and sorted by a key):

 my @datab;
 my $sql = q~
 SELECT table
 FROM table
 WHERE item1 = ? AND item2 = ?
 ORDER by item1
 ~;
 my $dbh = WebDB::connect();
 my $sth = $dbh->prepare($sql);
 $sth->execute( $item1,  $item2 );
 while ( my @val = $sth->fetchrow_array() ) {
     push @datab, [@val];
 }

 $sth->finish();
 $dbh->disconnect();
  

In this example, you can access the data in the array of arrays (@datab) by using:

for my $i (0 .. $#data) {
     my ( $newitem1, $newitem2 ) = @{$datab[$i]};
 ##Do something with each 'row' in @datab ##
 } 

This is a space to post Perl/DBI code for storage/sharing.

I'm working on converting some other of my sites to using mySQL for data storage instead of flat files so I'll be sharing bits of code that I've gotten to work.

1st: the lib/WebDB.pm file. (In YaBB it would go into either Sources or Modules.)

package WebDB;

 use strict;
 use DBI;

 my $host_name = 'localhost';
 my $db_name = 'mysite';
 my $userid = 'root';
 my $password = 'password';
 my $dsn = "DBI:mysql:host=$host_name;database=$db_name";

 sub connect {
     return (DBI->connect($dsn, $userid, $password,
     {PrintError => 0, RaiseError => 1}));
 }

 1; 

Notes on 'tables': A DB table is what PerlMongers call a hash of hashes - the values in the field assigned to be the 'index' is the 'name' of the hash while the table field names are the hash keys for all the hashes in the table.