#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use lib'/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/DBD/mysql.pm';

#retrieve names of bacteria and contaminants and store in arrays
my $bactfilename = "Bacteria_names3.txt";
my @bact_names = get_data($bactfilename);

my $contaminantname = "Contaminant_names4use.txt";
my @cont_names = get_data($contaminantname);

#database details:
my $ds = "DBI:mysql:Pmeddata:localhost";
my $user = "root";
my $passwd = "******";

#connect to database, prepare and execute SQL
my $dbh = DBI->connect($ds,$user,$passwd) || die "Cannot connect to database!!";

my $sth = $dbh->prepare("SELECT pmid, abstract FROM PM_text WHERE title LIKE '%E.coli%'"); 
$sth->execute;

#arrays to hold pmid, and abstract
my @abst_listing;
my @abst_PMID;

while (my @abstracts = $sth->fetchrow_array()){
		push(@abst_PMID,$abstracts[0]);
		push(@abst_listing,$abstracts[1]);
	}

$sth->finish;

my $sth2 = $dbh->prepare("INSERT INTO PM_bacteria (bactname, assoc_cont, pm_id) VALUES (?,?,?)");
my $sth3 = $dbh->prepare("INSERT INTO PM_cont (cont_name, pmed_id) VALUES (?,?)");

#WORKS WELL UP TO THIS POINT:THE FOLLOWING BIT DOES NOT WORK
#use nested 'for' loops for pattern matching
my $a; 
my $b; 
my $c;
for ($a=0; $a<= scalar(@abst_listing); $a++){
	for ($c=0; $c<= scalar(@cont_names); $c++){
		if($abst_listing[$a] =~ m/$cont_names[$c]/im){
			for($b=0; $b<= scalar(@bact_names); $b++){
				if($abst_listing[$a] =~ m/$bact_names[$b]/im){
					#insert into database;
					$sth2->execute($bact_names[$b],$cont_names[$c],$abst_PMID[$a]);
					$sth3->execute($cont_names[$c],$abst_PMID[$a]);
					print "matched at abst no $a , for $cont_names[$c] and $bact_names[$b]\n";
					}
					
				}
			}

		}
	}

$sth2->finish;
$sth3->finish;
$dbh->disconnect;



sub get_data{
	my ($filename) = @_;
	unless (open(DATAFILE, $filename)){
		print "Could not open file $filename!!\n";
		exit;
	}
	my (@filedata) = <DATAFILE>;
	close(DATAFILE);
	return @filedata;
}