GUFE Walkthrough: Part 2

#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");

We need to harvest the various parameters delivered to GUFE via CGI. Since we made these parameters up ourselves, we know what we need to collect. In the above passage, we retrieve a string of field names to return from SQL queries, the table name to query, whether to sort the table, and what type of sort -- ascending or descending.

#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);

We also want to collect the criteria parameters which customize the SQL query and determine what view of the table we receive. For instance, a user may use the criteria form fields to request a table in which we see only those records where the Total is greater than 100. These parameters are a bit more tricky because the parameter names are generated on-the-fly by GUFE based on the field labels contained in a particular table. To help standardize things, the criteria parameters are named criteria_condition_fieldLabel and criteria_value_fieldLabel. In the code above, we retrieve from the CGI object all parameters which conform to these names and we push those values onto a list named @allcriteria. It looks messy, but at the end we construct a single string by using a Perl join(), which might look something like:

Total > 100 AND Paid = 1

When all is said and done, we now have a variety of variables which contain the various parameters passed to GUFE. Basically, this is all about setting up the pins. Now we need to get the ball rolling.

#mission control ... set the ball 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));
}

The database connection is established, and the handle passed onto $dbh. Next, a main if clause determines whether the page request specified a table to view, via the table CGI parameter.

If yes, we continue processing by constructing an SQL statement using portions of CGI parameters we have harvested earlier. The SQL statement is then passed onto and executed by our &sendSQL subroutine and the statement handle is passed onto $sth. We'll need to use this handle to access the results of the query.

We end this if clause with a compound statement, one which executes our &createPage subroutine using the results of the &resultTable subroutine. We pass several parameters to &resultTable, including the database and SQL statement handles, the name of the table being queried, and the fields to be returned. This subroutine, which we'll see shortly, actually creates the entire HTML output of GUFE, which is then passed to &createPage in this case. For its part, &createPage takes the HTML produced by &resultTable and shoves it into the HTML template. Finally, the results of that are output to the screen by the containing print() function. Whoa!

In case the user did not specify a table to query (namely by visiting gufe.cgi with no additional parameters), the else clause skips construction of the SQL statement and proceeds directly to building and outputting the result table, which itself will alert the user to select a table to view.

Let's Walk GUFE
The Perl You Need to Know
GUFE Walkthrough: Part 3