Good Day all,
I’m a newbie to both sql and perl. I’m forcing my self to learn by creating programs that i would like to use.
I’m a little stuck on my first program. I’ve created a db that has a file path, file date, and date printed.
weekly i want to run a script that will look in a particular directory. Check all files added, print them, and add them to my db.
#!/usr/bin/perl
PERL MODULES WE WILL BE USING
use Mysql;
use DBD::mysql;
use DBI;
CONFIG VARIABLES
my $platform = “mysql”;
my $database = “timecards”;
my $host = “localhosts”;
my $port = “3306”;
my $tablename = “ttimecards”;
my $user = “perluser”;
my $pw = “password”;
my $startdir = ‘/home/mendezj/WA/TC’;
my ($n, $fn, @dirlist, @words);
DATA SOURCE NAME
$dsn = “dbi:mysql:$database:localhost:3306”;
PERL DBI CONNECT
$connect = DBI->connect($dsn, $user, $pw) or die “Unable to connect: $DBI::errstr\n”;
chdir($startdir);
opendir( DIRECTORY, ‘.’ );
my @allDirs = readdir( DIRECTORY );
closedir( DIRECTORY );
for( my $i = 0; $i <= $#allDirs; $i++ ) {
if( -d $allDirs[ $i ] ) {
next if $allDirs[$i] =~ /[.]$/;
push @dirlist, “$startdir/$allDirs[ $i ]”;
}
}
foreach (@dirlist) {
chdir($);
opendir( DIRECTORY, ‘.’ );
my @subdirs = readdir( DIRECTORY );
closedir( DIRECTORY );
foreach my $tmp (@subdirs) {
next if $tmp =~ /[.]$/;
my $fpath = "$/$tmp";
print “$fpath\n”;
my @stat = stat( “$_/$tmp” );
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($stat[10]);
my $dt = sprintf(“%4.4ld-%2.2ld-%2.2ld”, $year+1900, $mon+1, $mday);
my $dtc = “2007-06-05”;
#create a sql statement to see if $_/$tmp is in the database
$query = “SELECT * FROM $tablename WHERE path = ‘$fpath’”;
$query_handle = $connect->prepare($query);
$query_handle->execute() || die “Could not execute SQL statement … maybe invalid?”;
if yes do nothing if no print and add to the database
if ($query_handle = 1) {
print “need to add $fpath to the db\n”;
}
Since all files in my database are already printed. what i have done is create a new list of the items in my directory, create a query to see if the file path is already in the database, if the file path is in the database than the file was already printed so do nothing…if on the other hand the file is not in the db print the file and add the file, file date, and date printed to the data base.
what i was hoping to see from the following statement was
if ($query_handle = 1)
if my query is true that that should mean that the file is already in my database so do nothing. ELSE print and add to database.
any help would be much appreciated.