#!/usr/bin/perl
use DBI;
$dbh = DBI->connect("DBI:mysql:database=pfaf;host=localhost",
"anonymous", "password",
{'RaiseError' => 1});
print "Content-type: text/html\n\n";
print "";
if (!$dbh) {
print "Fatal: could not connect to mysql\n";
exit 1;
}
$base_url="/cgi-bin/pfaf\?$ENV{'QUERY_STRING'}";
foreach (split (/[&;]/, $ENV{'QUERY_STRING'})){
s/\+/ /g;
($name, $value)= split('=', $_, 2);
$name=~ s/%([0-9A-Fa-f]{2})/chr(hex($1))/ge ;
$value=~ s/%([0-9A-Fa-f]{2})/chr(hex($1))/ge ;
if ($value){
if ($name =~ /^exact/){
$select = $dbh -> prepare ("select * from species where latin_name = \"$value\" order by rating desc;");
}
if ($name =~ /^partial/){
$select = $dbh -> prepare ("select * from species where latin_name like \"\%$value\%\" order by rating desc;");
}
if ($name =~ /^common/){
$select = $dbh -> prepare ("select * from species where common_name like \"\%$value\%\" order by rating desc;");
}
if ($name =~ /^family/){
$select = $dbh -> prepare ("select * from species where family = \"$value\" order by rating desc;");
}
if ($name =~ /^sql/){
$select = $dbh -> prepare ("select * from species where $value order by rating desc;");
}
}
}
if (!$select){
print "Fatal: select from pages failed\n".$dbh -> errstr;
exit 1;
}
if (!$select ->execute){
print "Fatal: select from pages failed\n".$select -> errstr;
exit 1;
}
$rows = $select->rows;
$data=$select->fetchall_arrayref;
if ($rows >1){
print "Query returned $rows results.
";
print "
| Family | Botanical name | Common name |
";
$counter = 0;
while ($counter <= $rows){
print "| $$data[$counter][3] | ";
$wrinkled = $$data[$counter][0];
$wrinkled =~ s/ /\+/g;
print "$$data[$counter][0] | ";
print "$$data[$counter][4] |
";
$counter++;
}
print "
";
}
$counter = 0;
while ($counter < $rows){
$row = $$data[$counter];
$wrinkled = $$row[0];
$wrinkled =~ s/ /\+/g;
print "";
print "";
print "Common name: $$row[4]
";
print "Family: $$row[3]
";
print "Synonyms: $$row[39]
";
print "PFAF rating: $$row[50]
";
print "
";
if ($$row[24]=~/S/){
print "Slow ";
}elsif ($$row[24]=~/M/){
print "Moderate ";
}else{
print "Fast ";
}
print "growing ";
if ($$row[6]=~/E/){
print "Evergreen ";
}else{
print "Deciduous ";
}
print "$$row[5] to ".($$row[7]*3.3)." feet high and ".($$row[8]*3.3)." feet wide.
";
print "Grows wild in $$row[12] $$row[13]
";
print "Prefers ";
if ($$row[14]=~/L/){
print "sandy soil ";
}
if ($$row[14]=~/M/){
print "loam soil ";
}
if ($$row[14]=~/H/){
print "clay soil ";
}
print "with ";
if ($$row[19]=~/A/){
print "acid pH ";
}
if ($$row[19]=~/N/){
print "neutral pH ";
}
if ($$row[19]=~/B/){
print "alkaline pH ";
}
print "and ";
if ($$row[15]=~/N/){
print "no shade ";
}
if ($$row[15]=~/S/){
print "partial shade ";
}
if ($$row[15]=~/F/){
print "full shade ";
}
print "locations with ";
if ($$row[16]=~/D/){
print "little moisture ";
}
if ($$row[16]=~/M/){
print "moderate moisture ";
}
if ($$row[16]=~/We/){
print "wet/boggy moisture ";
}
if ($$row[16]=~/Wa/){
print "aquatic/pond moisture ";
}
print "levels.
";
print "Tolerates ";
if (!$$row[17]){
print "wet ground ";
}
if ($$row[20]){
print "acid pH ";
}
if ($$row[21]){
print "alkaline pH ";
}
if ($$row[22]){
print "saline soil ";
}
if ($$row[23]=~/W/){
print "very strong wind ";
}
if ($$row[23]=~/M/){
print "moderate wind ";
}
if ($$row[25]=~/Y/){
print "pollution ";
}
if ($$row[26]){
print "poor soil ";
}
if ($$row[27]){
print "drought ";
}
if ($$row[51]=~/Y/){
print "frost ";
}
if ($$row[46]){
print "heavy clay soil ";
}
print "conditions.
";
if ($$row[9]){
print "Hardy to zone $$row[9].
";
}
if ($$row[28]){
print "This plant supports wildlife.
";
}
if ($$row[18]){
print "This plant is a nitrogen fixer.
";
}
if ($$row[6]=~/D/ && $$row[32]){
print "In leaf months $$row[32].
";
}
if ($$row[53]){
print "Scented ";
}
if ($$row[35]=~/H/){
print "Hermaphrodite ";
}
if ($$row[35]=~/M/){
print "Monoecious ";
}
if ($$row[35]=~/D/){
print "Dioecious ";
}
print "flowers in bloom months $$row[33]. ";
if ($$row[37]=~/Y/){
print "Self fertile. ";
}
if ($$row[37]=~/N/){
print "Not self fertile. ";
}
print "Pollinators: $$row[36]. Seed ripens months $$row[34].";
print "Cultivation notes: $$row[40]
";
print "Propagation notes: $$row[43]
";
print "Edible uses: $$row[41]
";
print "Medical uses: $$row[11]
";
print "Other uses: $$row[42]
";
print "Hazards: $$row[38]
";
print "Cultivars:
";
$query = $dbh -> prepare ("select * from cultivars where latin_name = \"$$row[0]\"");
if (!$query){
print "Fatal: query from pages failed
".$dbh -> errstr;
exit 1;
}
if (!$query ->execute){
print "Fatal: select from pages failed
".$select -> errstr;
exit 1;
}
while ($row = $query->fetchrow_arrayref){
print "$$row[1]: $$row[2]
";
}
$counter++;
}
print "";