database question

[Brian]

My contribution, you said you were using (or going to use) MySQL, so
this *should* work pretty straight-forward.  You'll have to have your
MySQL tables setup beforehand.  If you're not so good with that, I'd
recommend Webmin, it has a nice MySQL GUI (I use it for one thing or
another almost everyday.  http://www.webmin.org ).  I personally setup
my tables so that the first field is always "unique_id", which is set to
auto_increment/primary key (I use an 8 digit int, filled with zeros). 
This tells MySQL to assign each table row a unique ID value as rows are
inserted into the db.  Makes it much easier to do deletes/manipulations
later without worry that you'll match 2 (or more) rows accidentally.

This script assumes the directory referenced contains only pdf files. 
If not, modify the match strings appropriately.  

I wrote this in email, ie: it hasn't been tested, so it might require a
slight tweakage.

#!/usr/bin/perl
use DBI;

# -----------------------------------------------------------------
# modify use db to the appropriate db (files or files_backup)
# this allows you to easily debug with a test db, while preserving the 
# production db.

$usedb="files";

# Yes, I know, we're technically going to refer to a table, not a db
# with this variable.
# -----------------------------------------------------------------

# modify the (hopefully obvious) portions of the following line as req'd
$dbh = DBI->connect('DBI:mysql:YOUR_DB_NAME_HERE' ,"user_name","pass");

opendir DIRH, "/path/to/files" || die "Cannot open: $!";
foreach (sort readdir DIRH) {
   if ($_ !~ m/^\.+$/) {
   
      # $filename holds the name of the file you are manipulating
      $filename = $_;
      $filename =~ s/\.pdf//;  
      # yes, we've done some extra steps above, but wanted to try to
      #maintain a little more clarity about whats going on

      #split on underscores and populate the 5 variables
      ($info1, $info2, $info3, $info4, $info5) = split (/_/,$filename);

      # now, prepare a db insert line:
      $sth = $dbh->prepare( "insert into $usedb (info1, info2, info3,
info4, info5, notes) VALUES
('$info1','$info2','$info3','$info4','$info5','Some other notes here')"
);

      #execute the db statement, and save it's return val into $return
      $return = $sth->execute();

      if ($return == 1 ) {
         print "DB insert completed for $filename\n";
         rename "/path/to/$filename", "/new/path/to/$filename";
      } else {
         print "DB insert failed for filename (bugger!)\n";
         print "File will be left in place\n";
      }
      $sth->finish();
   }
}
closedir DIRH;


On Sun, 2003-11-23 at 22:19, Vince McHugh wrote:
> Hi All,
> 
>    I'd like to preface this question by saying I don't
> know much about databases. But I am trying to work
> with a customer who is trying to do the following...
-- 
Brian <lists at karas.net>