#!/usr/bin/perl
################################################################################
#
# This script is the backend to the search form and performs the actual
# interaction with the database. Again, both the "strict" and "warnings" pragma
# have been turned on to increase the legibility of my code, and the CGI.pm
# module was used to simplify accessing the parameters passed by either GET or
# POST from dbhtml.cgi. Of course, just as in the other script, the
# "fatalsToBrowser" option allowed for more efficient code testing.
#
# Also in accordance with the intent of Perl to make easy problems easy and
# difficult ones possible, I have incorporated DBI.pm for simplified access to
# the database that I am set up for this semester. Currently, this is a MySQL
# server running on my personal computer (identified as 10.1.4.29 by the
# school's DHCP server as of this semester).
#
# The database is balled: botany
# The user who can access this database over a network connection is: anonymous
# This user's password is currently: oxalis
#
# Since this is still just a test script, and its sole purpose at this point
# is as a class project, I have not hidden the connection information, mostly
# because it has made my life easier whenever I chose to edit the database's
# name, location, or user access information to suit my newest whim.
#
################################################################################
use warnings;
use strict;
use CGI qw(param header);
use CGI::Carp qw(fatalsToBrowser);
use DBI;
################################################################################
### Define the variables for the database connection
my $database = param('database') || 'botany';
my $host = param('host') || '10.1.4.29';
my $username = param('username') || 'anonymous';
my $password = param('password') || 'oxalis';
################################################################################
### Print out the all important CGI header and begin HTML
print header();
print "Test Results from database [$database] on host [$host]\n";
print "\n";
################################################################################
### Connect to the $database on $host and login with $username and $password
my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host", $username, $password)
or die "Cannot connect to database: $DBI::errstr\n";
print "Connected to MySQL database [$database] on host [$host] @ ", scalar localtime, "\n
\n";
################################################################################
### All database interaction should occur here
###
### Currently, three different pieces of information are shown on the webpage
### 1) A list of all parameters passed in from the search form
### 2) A table containing the Common Names and Latin Names of the plants
### which meet the search criteria (inclusive OR search).
### 3) A listing of the full contents of the entire database
###
my @passedValues = ();
foreach (param()) {
if ($_) {
print "$_ : ", param($_), "
\n";
push(@passedValues, "$_:" . param($_));
}
}
database_interaction2(@passedValues);
database_interaction();
################################################################################
### Disconnect from the database (clean up after yourself)
$dbh->disconnect() or die "Disconnection failed: $DBI::errstr\n";
print "
\nDisconnected from MySQL database @ ", scalar localtime, "\n";
################################################################################
### End the HTML
print "\n";
################################################################################
### Subroutine Definitions Below Here...
################################################################################
#
# Currently, this particular script simply looks into the opened database and
# prints out all of its contents. And that, as they say, is that.
#
################################################################################
sub database_interaction {
my $sth;
my $table_name;
my @table_names = ();
my @fields = ();
$sth = $dbh->prepare("SHOW TABLES");
$sth->execute();
while ($table_name = $sth->fetchrow_array()) {
push(@table_names, $table_name);
}
my $i=0;
foreach (@table_names) {
$sth = $dbh->prepare("DESCRIBE $_");
$sth->execute();
my $field_name;
my @temp = ();
while (($field_name) = $sth->fetchrow_array()) {
push(@temp, $field_name);
}
push (@fields, \@temp);
$i++;
}
print "Tables in $database:";
foreach (@table_names) { print "$_ "; }
print "
\n";
print "Fields in tables:
\n";
foreach (@fields) {
foreach (@$_) {
print "$_ ";
}
print "
\n";
}
print "
\n";
print "prepare("SELECT * FROM $table_names[$i]");
$sth->execute();
print "Contents of table [$table_names[$i]]
\n";
my @row = ();
print "\n";
print "\t\n\t\t";
foreach (@{$fields[$i]}) { print "| $_ | "; }
print "\n\t
\n";
while (@row = $sth->fetchrow_array()) {
print "\t\n\t\t";
foreach (@row) {
print "| ";
if (!defined($_)) { print "NULL"; }
else { print "$_"; }
print " | ";
}
print "\n\t
\n";
}
print "
\n";
}
}
################################################################################
#
# This subroutine manages to determine all of the plants in the database
# which match ANY of the criteria selected on the search form (uses a classic
# OR search). Once it finds these values, it prints them out neatly in
# an HTML table.
#
################################################################################
sub database_interaction2 {
my @passedValues = @_;
my $sth;
my ($group, $trait);
my $gID;
my @tIDs = ();
my $pIDsref;
my @pIDs = ();
my $statement;
my ($commonName, $latinName);
my %matchingPlants = ();
foreach (@passedValues) {
($group, $trait) = split(/:/, $_);
$sth = $dbh->prepare("SELECT gID FROM groups WHERE Name = ?");
$sth->bind_param(1, $group);
$sth->execute();
($gID) = $sth->fetchrow_array();
$sth = $dbh->prepare("SELECT tID FROM traits WHERE Name = ? AND gID = ?");
$sth->bind_param(1, $trait);
$sth->bind_param(2, $gID);
$sth->execute();
push(@tIDs, $sth->fetchrow_array());
}
foreach (@tIDs) {
$sth = $dbh->prepare("SELECT pID FROM mappings WHERE tID = ?");
$sth->bind_param(1, $_);
$sth->execute();
$pIDsref = $sth->fetchall_arrayref();
foreach (@$pIDsref) {
push(@pIDs, @$_);
}
}
my @revisedPIDs = ();
my $originalValue;
foreach my $i (@pIDs) {
$originalValue = 1;
foreach my $j (@revisedPIDs) {
if ($i == $j) {
$originalValue = 0;
last;
}
}
push(@revisedPIDs, $i) if $originalValue;
}
foreach (@revisedPIDs) {
$sth = $dbh->prepare("SELECT Common_Name, Latin_Name FROM plants WHERE pID = ?");
$sth->bind_param(1, $_);
$sth->execute();
($commonName, $latinName) = $sth->fetchrow_array();
$matchingPlants{$commonName} = $latinName;
}
print "
Plants which match your search criteria...
\n";
my @row = ();
print "\n";
print "\t\n\t\t";
print "| Common Name | Latin Name | ";
print "\n\t
\n";
foreach (keys %matchingPlants) {
print "\t\n";
print "\t\t| $_ | $matchingPlants{$_} | \n";
print "\t
\n";
}
print "
\n
\n";
}