#!/usr/bin/perl
# Michael Tartaglia
# Working with a simple PSQL DBase


use Pg;
use CGI ':standard';
require template;

template::startHTML("Assignment 12","Working With A Database");

my ($sort, $what, $item) = (param("sort"),param("what"),param("query"));
my $i = 0;

$item =~ s/^(\w*|\d*)\s*(\w*|\d*)/$1$2/;	# ELIMINATE SPACES

if ($ENV{'QUERY_STRING'} eq "") {		# SHOW INSTRUCTIONS IF NO QUERY IS ENTERED
 print <<ENDOFHTML;

	<b>Look in the Formula 1 Racing database!</b><br>
	<i>This is for the 2002 season to date.</i><br>

	<FORM ACTION="database.cgi"><p align="justify">
	Enter a team name (like Ferrari or Jaguar), a driver's name (like 
	Barrichello or Button), a driver's number, or the three-letter 
	abbreviation for a country. Try a points range by typing the 
	minimum points value you desire (typing 0 will show all drivers, 
	typing 10 will only show those who have 10 points or more). Also 
	choose display order. (If you have no idea what Formula One is, 
	check out <a href="http://www.dailyf1.com">Daily F1</a> for an 
	idea - no, I did not create that site.)</p>
	<p align="justify"><font color="#800000">Sorry, but as of May 27, 
	2002, this database has been disabled. Please refer to my AOL
	page if you truly want to see the FIA World Championship standings
	for the 2002 season. <a href="http://members.aol.com/shmajent/f1.html">Click here to see.</a>
	</font></p>

	Enter query: <input type=text name="query" size=14><br><br>
	Search in what?<br><SELECT NAME="what">
	 <OPTION VALUE="">
	 <OPTION VALUE="name" selected>Driver's Name
	 <OPTION VALUE="number">Driver's Number
	 <OPTION VALUE="country">Driver's Country
	 <OPTION VALUE="team">Team's Name
	 <OPTION VALUE="points">Points
	</SELECT><br><br>
	Sort by what?<br><SELECT NAME="sort">
	 <OPTION VALUE="">
	 <OPTION VALUE="">Doesn't matter.
	 <OPTION VALUE="name">Driver's Name
	 <OPTION VALUE="number">Driver's Number
	 <OPTION VALUE="country">Driver's Country
	 <OPTION VALUE="team">Team's Name
	 <OPTION VALUE="points asc">Points (ascending)
	 <OPTION VALUE="points desc">Points (descending)
	</SELECT><BR><BR>
	<input type=submit><input type=reset>
	</FORM>

ENDOFHTML
} elsif ($what eq "" || $item eq "") {		# ELSE WARN IF BAD QUERY WAS MADE
 print "<font color=red>You've entered some bad data.</font><BR>",
       "Hit the back button or <a href=\"database.cgi\">click here</a>",
       "<br>";
} elsif (defined($what) && defined($item)) {	# ELSE ASSEMBLE SQL QUERY
 my $condition = "select * from tartagli ";

 if ($what eq "points") {
	$condition .= "where points >= " . int($item);
 } elsif ($what eq "number") {
	$condition .= "where number = " . int($item);
 } else {
	$item = "\U$item\E" if ($what eq "country");
	$condition .= "where $what like '%$item%'";
 }

 $condition .= " order by $sort" if ($sort ne "");

 my $database = Pg::connectdb("dbname=products");	# CONNECT TO DBASE
 my $connection = $database->exec("$condition;");
 my (@row, @data);

 $data[$i] = "<tr><th>$row[0]</th><td>$row[1]</td>"
	   . "<td>$row[2]</td><td align=center>$row[3]</td>"
	   . "<td align=center><i>$row[4]</i></td></tr>\n"
	   and ++$i while (@row = $connection->fetchrow);

 print "<table class=\"noBr\">\n",
       "<tr><th>Car #</th><th width=100>Name</th>",
       "<th>Nat.</th><th width=75>Team</th>",
       "<th>Points</th></tr>\n@data</table>\n<br>"
       if ($i > 0);

 print "<br>Your $what query of \"<i>$item</i>\" returned ",
       "<b>$i</b> results.";
 print "<br>Note that query is case sensitive!<br>"
       if ($i == 0 && $what ne "country");
}

template::endHTML();