LepQuery.pl
#! /usr/bin/perl -w
# use strict;
use CGI;
use DBI;
my $CGI = new CGI();
print $CGI ->header();
# open the accessDB file to retrieve the database name, host name,
# user name and password
open( ACCESS_INFO, "<../..\/accessDB" )
|| die "Can't access login credentials";
my $database = <ACCESS_INFO>;
my $host = <ACCESS_INFO>;
my $user = <ACCESS_INFO>;
my $passwd = <ACCESS_INFO>;
chomp( $database, $host, $user, $passwd );
close(ACCESS_INFO);
# display_form(); # see InsectPlant.htm
if ( $CGI->param() ) {
my $superfam = $CGI->param('superfam');
my $family = $CGI->param('family');
my $subfam = $CGI->param('subfam');
my $tribe = $CGI->param('tribe');
my $name = $CGI->param('name');
my $author = $CGI->param('author');
my $archi = $CGI->param('archipelago');
my $isle = $CGI->param('island');
my $region = 'French Polynesia';
my @args;
results_table();
############## Prepare query ############################
# set matching criteria for query string
my $sup = " LIKE '%$superfam%'";
my $fam = " LIKE '%$family%'";
my $sub = " LIKE '%$subfam%'";
my $tri = " LIKE '%$tribe%'";
my $nam = " LIKE '%$name%'";
my $aut = " LIKE '%$author%'";
# test for search criteria and create mysql search string
my $sort = "Name";
my $qsearch = "none";
my $psearch = "none";
my $qregion = "Region = French Polynesia";
#if ($superfam) {
# $qsearch = "Superfam $sup";
#}
if ($superfam) {
$qsearch = "Superfam LIKE ?";
push( @args, "%$superfam%" );
}
if ($family) {
if ( $qsearch eq 'none' ) {
$qsearch = "Family LIKE ?";
}
else {
$qsearch .= " && Family LIKE ?";
}
push( @args, "%$family%" );
}
if ($subfam) {
if ( $qsearch eq 'none' ) {
$qsearch = "Subfam LIKE ?";
}
else {
$qsearch .= " && Subfam LIKE ?";
}
push( @args, "%$subfam%" );
}
if ($tribe) {
if ( $qsearch eq 'none' ) {
$qsearch = "Tribe LIKE ?";
}
else {
$qsearch .= " && Tribe LIKE ?";
}
push( @args, "%$tribe%" );
}
if ($name) {
if ( $qsearch eq 'none' ) {
$qsearch =
"Name IN "
. "(SELECT DISTINCT Name "
. "FROM `Lepsyn` WHERE Synonym LIKE ?)";
}
else {
$qsearch .= " && "
. "Name IN (SELECT DISTINCT Name "
. " FROM `Lepsyn` "
. " WHERE Synonym "
. " LIKE ?)";
}
push( @args, "%$name%" );
}
if ($author) {
if ( $qsearch eq 'none' ) {
$qsearch = "Author LIKE ?";
}
else {
$qsearch .= " && Author LIKE ?";
}
push( @args, "%$author%" );
}
if ( $archi ne 'none' ) {
$psearch = "Archipelago = ?";
$qregion = "Archipelago = $archi";
push( @args, "$archi" );
}
if ( $isle ne 'none' ) {
$psearch = "Island = ?";
$qregion = "Island = $isle";
push( @args, "$isle" );
}
if ( ( $qsearch ne 'none' ) && ( $psearch ne 'none' ) ) {
$qsearch .= " AND "
. "Name IN (SELECT DISTINCT Name "
. " FROM `Lepdis` "
. " WHERE $psearch)";
}
if ( ( $qsearch eq 'none' ) && ( $psearch ne 'none' ) ) {
if ( $archi ne 'none' and $isle ne 'none' ) {
print '<a href="FPLeps.htm"
target="_parent">
Select one of Archiplago or Island, not both - NEW SEARCH</a>';
#$qsearch = "Name IN (SELECT DISTINCT Name " .
# "FROM `Lepdis` WHERE Archipelago = ? " .
# "AND Island = ?)";
#push(@args,$archi);
#push(@args,$isle);
}
elsif ( $archi ne 'none' ) {
$qsearch = "Name IN (SELECT DISTINCT Name "
. " FROM `Lepdis` "
. " WHERE Archipelago = ?)";
}
elsif ( $isle ne 'none' ) {
$qsearch = "Name IN (SELECT DISTINCT Name "
. " FROM `Lepdis` "
. " WHERE Island = ?)";
}
}
if ( ( $qsearch ne 'none' ) && ( $psearch eq 'none' ) ) {
$qsearch .= " AND "
. "Name IN (SELECT DISTINCT Name "
. " FROM `Lepdis` "
. " WHERE Region = ? )";
push( @args, $region);
}
if ( ( $qsearch eq 'none' ) && ( $psearch eq 'none' ) ) {
print '<a href="FPLeps.htm"
target="_parent"> No search criteria entered - NEW SEARCH</a>';
}
else {
print <<EOT;
<p align="center"><font size="+3" face="Times New Roman, Times, serif">
Lepidoptera of French Polynesia - Search Results</font></p>
<p align="center"><a
href="FPLeps.htm"
target="_parent">NEW SEARCH</a></p>
<p align="left">$qregion</p>
EOT
}
################### Access MySQL database ######################
my $dbconnection = "$database:$host:3306";
my $data_source = "dbi:mysql"; #define a mysql database
my $dbh = DBI->connect( "$data_source:$dbconnection", $user, $passwd )
or die "failed to connect to $dbconnection\n";
my $statement = "SELECT Name, Author, Tribe, Subfam, "
. " Family, Superfam, Photo "
. "FROM Leptax "
. "WHERE $qsearch "
. "ORDER BY $sort ";
############ Process query results ############################
#debugging block
print <<EOdebug;
<p>
\$psearch:
$psearch
<br/>
\$qsearch:
$qsearch
<br/>
\$statement:
$statement
<br/>
\@args:
@args
</p>
EOdebug
local $" = ')(';
print "\@args contains: ", scalar(@args), " elements:\n";
print "(@args)\n";
my $cursor = $dbh->prepare($statement);
$cursor->execute(@args);
while ( my $query = $cursor->fetch ) {
print <<EOT;
<tr>
<td width="150"> <img src="$query->[6]" width="150" height="100"></td>
<td width="100"> $query->[0] </td>
<td width="100"> $query->[1] </td>
<td width="75"> $query->[2] </td>
<td width="75"> $query->[3] </td>
<td width="75"> $query->[4] </td>
<td width="75"> $query->[5] </td>
<td width="50"> <a href="LepDetail.pl?
Name=$query->[0]&
Author=$query->[1]&
Tribe=$query->[2]&
Subfamily=$query->[3]&
Family=$query->[4]&
Superfamily=$query->[5]&
Photo=$query->[6]"
target="_parent">full record</a></td>
</tr>
EOT
}
print '</table>';
print '</td>';
print '</tr>';
print "<BR>";
print '<p><a href="FPLeps.htm" target="_parent">NEW SEARCH</a></p>';
$cursor->finish(); # close off cursor
$dbh->disconnect(); # close off database
}
############## Set up results table #########################
sub results_table {
print "<BR>";
print '<table width="98%" border="1">';
print '<tr>';
print '<td width="150"><b>Photo</b></td>';
print '<td width="100"><b>Name</b></td>';
print '<td width="100"><b>Author</b></td>';
print '<td width="75"><b>Tribe</b></td>';
print '<td width="75"><b>Subfamily</b></td>';
print '<td width="75"><b>Family</b></td>';
print '<td width="75"><b>Superfamily<b></td>';
print '<td width="50"><b>Full Record</b></td>';
print '</tr>';
}