PepperSearch.pl

#!/usr/local/bin/perl -w
use strict;
use Win32::ODBC;
use CGI qw(:standard);
#***** open database connection *****
my($DSN) = "Article";
my($db) = new Win32::ODBC($DSN) ||	die qq{Cannot open ODBC connection to "$DSN":}, Win32::ODBC::Error, qq{\n};
#***** get incoming CGI params *****
my($query) = new CGI;
my($submit, $pid, $heat, $start, $maxrows);
$submit = $query->param('submit');
$pid = $query->param('pid');
$heat = $query->param('heat');
$start = $query->param('start');
$maxrows = $query->param('maxrows');
$pid = $$ if ! $pid;
#***** globally used *****
my($rc, $stmt, $num_rows, $end);
$num_rows = 10;
#***** determine scoville limits *****
my($max, $min);
if ($heat eq 'MILD') {
	#***** just a hint of pepper *****
	$min = 0;
	$max = 500;
}
elsif ($heat eq 'MEDIUM') {
	#***** where most people like it *****
	$min = 500;
	$max = 2500;
}
elsif ($heat eq 'HOT') {
	#***** finally, some flavor! *****
	$min = 2500;
	$max = 10000;
}
elsif ($heat eq 'SCORCHER') {
	#***** now we're talking *****
	$min = 10000;
	$max = 100000;
}
else {
	#***** implied: $heat eq 'INSANE' *****
	$heat = 'INSANE';
	
	#***** no limit.... *****
	$min = 100000;
	$max = 0;
}
#***** what button was used? *****
if ($submit eq 'new') {
	#***** submit: new *****
	#***** delete old search results from State table *****
	$stmt = "DELETE FROM State WHERE PID = $pid";
	$rc = $db->Sql($stmt);
	die qq{SQL failed "$stmt": }, $db->Error(), qq{\n} if $rc;
	
	#***** fill State table *****
	#***** get second DB connection *****
	my($ins) = new Win32::ODBC($db);
	
	#***** perform new search *****
	#***** build SQL statement *****
	$stmt = "SELECT ProductID FROM Sauces " .
			"WHERE Scoville >= $min ";
			
	#***** add in maximum value (if there is one) *****
	$stmt .= "AND Scoville < $max " if $max;
	
	#***** add sequence request *****
	$stmt .= "ORDER BY Scoville, ProductName";
	
	#***** compile the SQL statement *****
	$rc = $db->Sql($stmt);
	die qq{SQL failed "$stmt": }, $db->Error(), qq{\n} if $rc;
	
	#***** loop thru all rows *****
	my($cnt, $id) = 0;
	while ($db->FetchRow) {
		#***** get Product ID *****
		($id) = $db->Data;
		
		#***** immediately insert into State table *****
		$stmt = "INSERT INTO State (PID, ProductID, Sequence) " .
			"VALUES ($$, $id, " . $cnt++ . ")";
			
		$rc = $ins->Sql($stmt);
		die qq{SQL failed "$stmt": }, $ins->Error(), qq{\n} if $rc;
	}
	
	#***** set start, maxrows and pid *****
	$start = 0;
	$pid = $$;
	$maxrows = $cnt;
}
elsif ($submit eq 'next') {
	#***** submit: next *****
	$start += $num_rows;
}
else {
	#***** submit: prev *****
	$start -= $num_rows;
}
	
#***** check min and max *****
$start = $maxrows - $num_rows if $start > $maxrows;
$start = 0 if $start < 0;
$end = ($start + $num_rows > $maxrows ? $maxrows : $start + $num_rows);
#***** print HTML headers *****
print qq(<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">\n\n);
print qq(<HTML><HEAD>\n);
print qq(<TITLE>Peppers, Inc. Search Results</TITLE>\n);
print qq(</HEAD>\n);
print qq(<BODY>\n);
print qq(<H1 ALIGN=center>Peppers, Inc. Search Results</H1>\n);
#***** print search results info *****
$max -= 1 if $max;
print qq(Search: $heat [$min), ($max ? " - $max" : "+"), qq( units Scoville]<BR>\n);
print qq(Search results: ), $start + 1, qq( - $end of $maxrows<BR>\n);
#***** start form, table *****
print qq(<FORM METHOD=POST ACTION="http://www.foobar.com/cgi-bin/PepperSearch.pl">\n);
print qq(<TABLE BORDER=1>\n);
print qq(<TR><TH>Scoville<TH>Name<TH>Pepper<TH>Quantity<TH>Price\n);
#***** fetch Sauces data based on State table data *****
$stmt = "SELECT sa.* FROM State st " .
	"INNER JOIN Sauces sa ON st.ProductID = sa.ProductID " .
	"WHERE st.PID = $pid " .
		"AND st.Sequence >= $start " .
		"AND st.Sequence < $end " .
	"ORDER BY st.Sequence";
	
#***** compile the SQL statement *****
$rc = $db->Sql($stmt);
die qq{SQL failed "$stmt": }, $db->Error(), qq{\n} if $rc;
#***** fetch each row *****
while ($db->FetchRow) {
	my(%data) = $db->DataHash;
	#***** output HTML ****
	print qq(<TR><TD>$data{'Scoville'}<TD>$data{'ProductName'}),
		qq(<TD>$data{'PepperType'}<TD>$data{'Quantity'}<TD>$data{'Price'}\n);
}
#***** end table && print hidden form elements
print qq(</TABLE>\n);
print qq(<P>\n);
print qq(<INPUT TYPE=HIDDEN NAME="pid" VALUE="$$">\n);
print qq(<INPUT TYPE=HIDDEN NAME="heat" VALUE="$heat">\n);
print qq(<INPUT TYPE=HIDDEN NAME="start" VALUE="$start">\n);
print qq(<INPUT TYPE=HIDDEN NAME="maxrows" VALUE="$maxrows">\n);
#***** print buttons (next, prev, new) *****
print qq(<INPUT TYPE=SUBMIT VALUE="Prev $num_rows" NAME="prev">\n)
	if ($start > 0);
print qq(<INPUT TYPE=SUBMIT VALUE="Next $num_rows" NAME="next">\n)
	if ($end < $maxrows);
print qq(<P>\n) if ($start > 0) || ($end < $maxrows);
print qq(<INPUT TYPE=SUBMIT VALUE="New Search" NAME="new">\n);
print qq(<INPUT TYPE=RADIO VALUE="MILD" NAME="heat"> Mild\n);
print qq(<INPUT TYPE=RADIO VALUE="MEDIUM" NAME="heat"> Medium\n);
print qq(<INPUT TYPE=RADIO VALUE="HOT" NAME="heat"> Hot\n);
print qq(<INPUT TYPE=RADIO VALUE="SCORCHER" NAME="heat"> Scorcher\n);
print qq(<INPUT TYPE=RADIO VALUE="INSANE" NAME="heat" CHECKED> Insane\n);
#***** all done *****
print qq(</FORM></BODY><HTML>\n);


Copyright © Joseph L. Casadonte Jr. 1998. All rights reserved.
PepperSearch.pl / 17 January 1998 / joc@netaxs.com