GUFE Walkthrough: Part 3 - Page 4

Click here for code example 4.

Establishing the connection to the database is fairly simple, and that's just what the &openDB subroutine does. Four parameters are sent to this subroutine when it is called -- the DBD protocol to use (such as "mysql" or "ODBC"), the database to connect to (such as "Clients"), and if necessary a username and password. Some databases, such as those in MySQL, can be protected requiring login. You can see that parameters passed to a subroutine wind up in the Perl built-in variable @_, a list of each value sent in.

If for some reason the connection should fail, the subroutine "dies", meaning that it exits to the browser with an error message.

Click here for code example 5.

The &sendSQL subroutine, while extremely important to GUFE, is also quite simple. That's because interpreting and processing SQL statements is the job of the database itself. In short, &sendSQL receives the SQL statement and the database handle. Then, using standard DBI syntax, it prepares and executes the SQL statement at the database engine. The final result of this is a statement handle, which is returned from the subroutine, and which we later use to access the actual data returned by the database in response to the query. It is with this statement handle, $sth, that we'll do much of our work.

The workhorse subroute of GUFE is &resultTable, which parses through the data returned by the database and constructs an HTML table out of it all. Because this subroutine is on the longer side, let's take it on in bite sized chunks.

Click here for code example 6.

Several initial variables are setup which will be used in creating the HTML, including $rowcount, $labelRow, $tableHTML, and so on. The first real work we do, however, is to construct a URL query string. The URL query string is all the stuff that appears after the ? in a URL, such as in:

http://www.somesite.com/cgi-bin/script.cgi?blah=1&boo=2

In the above example, "blah=1&boo=2" is the query string. We need to construct a query string that replicates the state of GUFE -- that is, what table is being viewed, what are the criteria in action, and so forth. We do this because when we rig hyperlinks such as the field labels, to sort the table, we want to preserve these various states in the page that is returned. That is, we want to preserve which table is being viewed, what criteria are being used, etc. Continuing on ...

if ($queryTable){
while (my $row=$sth->fetchrow_hashref) {

This while loop will, on each pass through, retrieve the next record from the database. This assumes that the database has returned at least one matching record -- if it hasn't, this whole while loop will never execute and we'll drop down further below in the subroutine. The record is returned from the database as a hash reference. A hash is a set of associated key-value pairs, as we saw in Part 1 of the Perl You Need to Know. With DBI, the returned hash contains each field name associated with its value. The reference to this hash is then stored in $row. Notice that $row is a scalar variable, not a hash (which would begin with a %) ... that is because $row merely contains a reference, or pointer, to the hash, not the actual hash itself.

#retrieve each record row-by-row from the database
#build HTML table row-by-row using this data
 $tableHTML.="<TR>";
 @fieldNames=keys %$row; #build a list of field labels
 $fieldCount=$#fieldNames+1;
 foreach my $fieldName (@fieldNames) {
  unless ($rowcount>0) {
	my $queryURLb=$queryURLa."&sort=$fieldName";
	if ($querySortType eq "ASC") {
	 $queryURLb.="&sortType=DESC"
	}
	else { $queryURLb.="&sortType=ASC" }
   #wrap hyperlink around field names, rigged with sort parameters
   $labelrow.="<TD><A HREF=\"/cgi-bin/gufe.cgi?$queryURLb\">".
              "<B>$fieldName</B></A></TD>";
  }

Here we loop through each field label as returned from the database. In doing so, we create the first row of the table, and wrap each field label inside a hyperlink. The hyperlink is rigged to connect back to GUFE with the same parameters as presently, with the addition of a sort parameter. The sort type is set to the opposite of whatever the current sort type is (ascending or descending). We only need to create the field label row once, not each time a record is retrieved, ensured by the unless ($rowcount>0) condition.

  if ($fieldName eq "Invoice") {
   #wrap hyperlink around invoice data, rigged to invoice table
   $tableHTML.="<TD><A HREF=\"/cgi-bin/gufe.cgi?".
               "table=invoices&criteria_condition_Invoice=%3D".
               "&criteria_value_Invoice=$row->{$fieldName}\">".
               "$row->{$fieldName}</A></TD>";
   }
  else {
   $tableHTML.="<TD>$row->{$fieldName}</TD>";
  }

The above excerpt is the least "generic" code in GUFE. Specifically, it traps for a field labeled Invoice, and it wraps such data inside a hyperlink that is rigged to pull up that Invoice record in the invoices database. This is not generic because we can't assume that any database contains an Invoice field, let alone one which is keyed between two tables. The code has been kept in GUFE to illustrate, though, how you might rig your own database in this fashion, to allow the user to quickly move between related records.

If the field label is not trapped as "Invoice", our normal, generic processing occurs. A table column is created into which the value for that field is placed. Note the syntax for retrieving the field value from the hash reference:

$row->{$fieldName}

Of course, you can explicitly request the value for a given field as well:

$row->{ClientEmail}

The important thing to remember is that $row is a variable of our choosing, to which we assigned the fetchrow_hashref procedure of the $sth statement handle.

}
$tableHTML.="</TR>";
$rowcount++;
}

if ($rowcount==0) {
 #database did not return any records
 $tableHTML.="<TR><TD>There is no table view that matches ".
             "your selected criteria.</TD></TR>";
}
} #end if wrapper
else {
	#no table has been selected to view
	$queryTable="None Selected"; }

It is possible that the user's selected criteria won't return any records from the database, in which case $rowcount would never have been incremented, and we can return a "no match" message rather than the database table, as seen above.

The else clause above is actually the partner of a much earlier if clause, that which tested whether any table had been requested at all. If no table were requested, we modify $queryTable to "None Selected", which will later appear as the title of the empty table that is produced.

#generate hyperlinks to other tables in this database
@tableNames=$dbh->tables;
$tableHTML.="<TR><TD COLSPAN=$fieldCount><BR>".
            "<FONT FACE=\"Arial,Helvetica\">".
            "Available tables to view: ";
foreach $tableName (@tableNames) {
 unless (($tableName eq $queryTable)&&($rowcount>0)) {
  $tableHTML.="<A HREF=\"/cgi-bin/gufe.cgi?".
  "table=$tableName\">$tableName</A> ";
 }
}
$tableHTML.="</FONT></TD></TR>";

$tableHTML="<TABLE width='75%' border=0 bgcolor='#FFFFCC'>".
           "<CAPTION><B><I>Table: $queryTable</I></B>"."
           "</CAPTION><TR>$labelrow</TR>$tableHTML</TABLE>";

In the lower region of the database output, GUFE offers hyperlinks to any other available tables in the database. GUFE determines the list of available tables using the DBI tables procedure. Note that this procedure is only supported in the most recent versions of DBI, and it may not be supported in the DBD module for your particular database (it is supported by the most recent DBD modules for ODBC (Access) and MySQL). If your DBI/DBD combination does not support tables then no list of tables will appear, since the list @tableNames will wind up empty.

The HTML table is finished off by prepending some HTML to begin the table and include the table name in the table's caption.

GUFE has now constructed the HTML that comprises the yellow table -- in other words, the table returned from the database. We also want to offer a form with selectable criteria for the user to construct a query, also known as the blue region. It might be worthwhile to construct this form as a separate chunk of HTML, and insert it into the HTML template with a second placeholder ... but for simplicity we simply add this nugget of HTML onto the existing $tableHTML.

unless (($queryTable eq "None Selected")||($rowcount==0)) {
 #construct criteria customization form
 $tableHTML.=
    "<P><FONT face=\"Arial, Helvetica, sans-serif\" size=\"-1\">".
    "Customize the table view using the fields below.".
    "<br>You must put single quotes around text values.".
    "<br>Click Apply with all set to IGNORE to view whole table.".
    "</FONT></P>";
 $tableHTML.=
    "<FORM method='get' action='/cgi-bin/gufe.cgi'>";
 $tableHTML.=
    "<TABLE width='75%' border=0 bgcolor='#CCFFFF'>";
 foreach $fieldName (@fieldNames) {
 $tableHTML.=
    "<TR><TD>$fieldName</TD><TD>";
 $tableHTML.=
    "<select name=\"criteria_condition_$fieldName\">".
    "<option value=\"0\">IGNORE</option>".
    "<option value=\"=\">equal to</option>".
    "<option value=\"like\">like</option>".
    "<option value=\">=\">greater/equal to</option>".
    "<option value=\"<=\">less/equal to</option>".
    "<option value=\">\">greater than</option>".
    "<option value=\"<\">less than</option></select></TD>";
 $tableHTML.=
    "<TD><INPUT type=text width=10".
    " name=\"criteria_value_$fieldName\">".
    "</TD></TR>";
 }
 $tableHTML.=
    "<TR><TD colspan=3>Combine criteria with ".
    "<select name=\"criteria_logic\">".
    "<option value=\"AND\">AND</option>".
    "<option value=\"OR\">OR</option>".
    "</select></TD></TR>";
 $tableHTML.=
    "</TABLE><INPUT type=hidden name=\"table\" value=$queryTable>".
    "<INPUT type=hidden name=\"return\" value=$returnFields>".
    "<INPUT type=submit value=\"Apply Criteria\"></FORM>";
         
 }#end unless wrapper
return $tableHTML
} #end resultTable

The above segment appears messy, but it's none too magical. Mostly, we construct HTML code containing a table nested inside a form. The table contains a row for each field label in the yellow (upper) table. Each row consists of three columns: the field label, a drop down select field, and a text input field. This allows the user to construct a simple condition for each field, such as "Total > 50".

The final line of code, "return $tableHTML" may seem slight but it is crucial. This returns the entire chunk of HTML we've created to the calling statement. Remember that the calling statement in this case was &createPage. In essence, this is like a baton relay, with &resultTable handing off the baton to &createPage.