#!/usr/bin/perl
#GUFE: Generic but Usable Front End
#by Aaron Weiss (aaron@pobox.com) for the Web Developers' Virtual Library
#http://www.wdvl.com
#You are encouraged to learn from, adapt, modify, and otherwise customize this script to your minds' content.
use CGI;
use DBI;
#setup database parameters
my $DBD="ODBC";
my $dbase="Clients";
my $dbuser="";
my $dbpassword="";
#prepare for output to browser
$cgiobj=new CGI;
print $cgiobj->header;
#retrieve CGI parameters
my $returnFields=$cgiobj->param("return");
unless ($returnFields) { $returnFields="*" }
my $queryTable=$cgiobj->param("table");
my $querySort=$cgiobj->param("sort");
my $querySortType=$cgiobj->param("sortType");
#parse through criteria parameters
my @allcriteria=();
my @allparams=$cgiobj->param();
foreach $param (@allparams) {
if (($param=~/criteria_condition_(.*)/)&&($cgiobj->param($param) ne "0")) {
my $criterion=$1." ".$cgiobj->param($param)." ".$cgiobj->param("criteria_value_$1");
if ($cgiobj->param("criteria_value_$1")) { #ignore blank criteria
push (@allcriteria,$criterion);
}
}
}
$queryCriteria=join(" ".$cgiobj->param("criteria_logic")." ",@allcriteria);
#mission control ... set the balls in motion
my $dbh=&openDB($DBD,$dbase,$dbuser,$dbpassword);
if ($queryTable) {
my $sqlstatement="select $returnFields from $queryTable";
if ($queryCriteria) {
$sqlstatement.=" where ($queryCriteria)" }
if ($querySort) {
$sqlstatement.=" order by $querySort $querySortType" }
my $sth=&sendSQL($sqlstatement,$dbh);
print &createPage(&resultTable($dbh,$sth,$queryTable,$returnFields));
}
else {
#no table has been selected to view
print &createPage(&resultTable($dbh));
}
sub openDB {
#open connection to database
my ($DBD,$dbase,$dbuser,$dbpassword)=@_;
my $dbh = DBI->connect("dbi:$DBD:$dbase",$dbuser,$dbpassword);
return $dbh;
} #end openDB
sub sendSQL {
#send SQL query
my ($sqlstatement,$dbh)=@_;
my $sth = $dbh->prepare($sqlstatement);
$sth->execute ||
&sqlAbort("Could not execute SQL statement ... maybe invalid?
Here is the statment that choked: $sqlstatement");
return $sth;
}#end sendSQL
sub resultTable {
#output SQL statement results into an HTML table
my $rowcount=0;
my ($dbh,$sth,$queryTable,$returnFields)=@_;
my $tableHTML="";
my $labelrow="";
my @fieldNames=();
my $fieldCount=0;
#reconstruct the query URL to pass on via hyperlinks
my $queryURLa="table=$queryTable&return=$returnFields";
if ($queryCriteria) {
my @allparams=$cgiobj->param();
foreach my $param (@allparams) {
if ($param=~/criteria/) {
$queryURLa.="&$param=".$cgiobj->param($param)
}
}
}
if ($queryTable){
while (my $row=$sth->fetchrow_hashref) {
#retrieve each record row-by-row from the database
#build HTML table row-by-row using this data
$tableHTML.="
Customize the table view using the fields below.".
"
You must put single quotes around text values.".
"
Click Apply with all set to IGNORE to view whole table.