CGI and Perl

Searching the Database for Keywords and Outputting Results

Listing 8.5 uses DBI to query the database about an item based on parameters specified in an HTML form. The data returned is formatted into HTML output in a similar fashion as in Listing 8.3. Figures 8.3 and 8.4 show the HTML output generated by this program. If no input is sent to the CGI, only the search form is displayed; however, if URI encoded data is detected by CGI.pm in the URI, that data is operated on, the database is queried, and search results are displayed. Notice extensive use of CGI.pm in this example.

Listing 8.5. Searching the database for keywords

1:   #!/usr/local/bin/perl
 2:   #
 3:   #  Search the AV Reference Room
 4:   #
 5:   #  by Tom White
 6:   #
 7:   #  $Id: search.cgi,v 1.6 1996/08/28 13:42:13 avlibrar Exp avlibrar $
 8:   #
 9:   open (STDERR, ">&STDOUT");
 10:  select STDERR; $| = 1;
 11:  select STDOUT; $| = 1;
 12:  use CGI;
 13:  $ENV{`PATH'} = `/usr/local/bin:/usr/ucb:/usr/bin:/usr/ccs/bin:/usr/sbin';
 14:  $ENV{`MANPATH'} =`/usr/local/man:/usr/man';
 15:  $ENV{`PAGER'} = `/usr/bin/cat';
 16:  $ENV{`SHELL'} = `/bin/sh';
 17:  $ENV{`IFS'} = ``;
 18:  $query = new CGI;
 19:  # build a WHERE clause for mSQL
 20:  # note: mSQL evaluates compound conditions strictly left-to-right
 21:  $where_string = "";
 22:  $conjunc = "WHERE ";
 23:  $fstring = $query->param(`string');
 24:  if ( $fstring ) {
 25:    $fstring = &prep_regex ($fstring);
 26:    $where_string = $where_string . $conjunc .
 27:      "title LIKE $fstring OR subtitle LIKE $fstring OR narrative LIKE $fstring";
 28:    $conjunc = " AND ";
 29:  }
 30:  $fmedium = $query->param(`medium');
 31:  if ( $fmedium && $fmedium ne `Any' ) {
 32:    $where_string = $where_string . $conjunc . "type = `$fmedium'";
 33:    $conjunc = " AND ";
 34:  }
 35:  $fpuber = $query->param(`puber');
 36:  if ( $fpuber ) {
 37:    $fpuber = &prep_regex ($fpuber);
 38:    $where_string = $where_string . $conjunc . "publisher LIKE $fpuber";
 39:    $conjunc = " AND ";
 40:  }
 41:  $faud = $query->param(`aud');
 42:  if ( $faud && $faud ne `Any' ) {
 43:    $where_string = $where_string . $conjunc . " $faud <> NULL";
 44:    $conjunc = " AND ";
 45:  }
 46:  if ( $where_string ) {
 47:    $hit_limit = $query->param(`hitlimit') || "25";
 48:    &pheader;
 49:    &search;
 50:    &ptrailer;
 51:  } else {
 52:    &pheader;
 53:    &ptrailer;
 54:  }
 55:  exit;
 56:  #prepare a search string for use in mSQL LIKE
 57:  sub prep_regex {
 58:    $_[0] =~ s/\[/\[\[\]/g;
 59:    $_[0] =~ s/\\/\[\\\\\\\\\]/g;           # no good - backslash breaks LIKE
 60:    $_[0] =~ s/([\$%\(\)\?\^|])/\[$1\]/g;
 61:    $_[0] =~ s/\'/\\\'/g;
 62:    $_[0] =~ s/_/\\\\_/g;
 63:    $_[0] =~ s/([A-Za-z])/\[\l$1\u$1\]/g;
 64:    return "`%" . $_[0] . "%'";
 65:  }
 66:  #search the database
 67:  sub search {
 68:    use DBI;
 69:    $drh = DBI->install_driver( `mSQL' )
 70:            || die "Could not install: $DBI::errstr";
 71:    $dbh = $drh->connect( ``, `AV_LIBRARY' )
 72:            || die "Could not connect: $DBI::errstr";
 73:    $sth = $dbh->prepare( "SELECT call_num, title, subtitle FROM catalog
 74:                           $where_string LIMIT $hit_limit" )
 75:            || die "Could not start select: $DBI::errstr";
 76:    $sth->execute || die "Could not select: $DBI::errstr";
 77:    print "<b>Search Results</b>\n<ol>\n ";
 78:    while ( ($call_num, $title, $subtitle) = $sth->fetchrow ) {
 79:      print qq!  <li><tt><a href="display.cgi?cn=$call_num">$call_num</a></tt>
 80:               $title\n!;
 81:      if ( $subtitle ) {
 82:        print " $subtitle\n";
 83:      }
 84:      $hits++;
 85:    }
 86:    print "</ol>\n";
 87:    if ( ! $hits ) {
 88:      print "<b>No</b> matches\n";
 89:    } elsif ( $hits >= $hit_limit ) {
 90:      print "Search stopped after first <b>$hits</b> matches.\n";
 91:    }
 92:    $sth->finish || die "Could not finish select: $DBI::errstr";
 93:  }
 94:  #send the header
 95:  sub pheader {
 96:    print $query->header;
 97:    print <<EOD;
 98:  <HTML>
 99:  <HEAD>
 100:   <TITLE>MCES's Audiovisual Reference Room - Search</TITLE>
 101:    <LINK REV="MADE" HREF="mailto:avlibrar\@ces.msstate.edu">
 102:    <META NAME="MarkUp" CONTENT="Tom White">
 103:  </HEAD>
 104:  <BODY>
 105:  <h1><img src="/pics/ces-b1mt.gif" width=499 height=53
 106:           alt="Cooperative Extension Service -
 107:               Mississippi State University">
 108: </h1>
 109: <h2>
 110:   Audiovisual Reference Room
 111: </h2>
 112: <h3>
 113:   Search
 114: </h3>
 115: EOD
 116: print $query->startform(`GET', `search.cgi', `""`);
 117: print "<b>String:</b> ";
 118: print $query->textfield(-name=>`string', -default=>``, -size=>15,
 119:                         -maxlength=>30);
 120: print "    (Must appear in either title or narrative.)\n";
 121: print "<br><b>Medium:</b> ";
 122: print $query->popup_menu(-name=>`medium', -default=>`Any',
 123:                          -values=>[`Any','Film','Slide','Audio','Video']);
 124: print "\n<br><b>Publisher:</b> ";
 125: print $query->textfield(-name=>`puber', -default=>``, -size=>15,
 126:                         -maxlength=>15);
 127: print "    (Must appear under Publisher.)\n";
 128: print "<br><b>Audience:</b> ";
 129: print $query->popup_menu(-name=>`aud', -default=>`Any',
 130:       -values=>[`Any','kinder','primer','element','junior',
 131:                 `senior','college','adult'],
 132:       -labels=>{`Any'=>,'Any',
 133:                 `kinder'=>,'Kindergarten (ages 3-5)',
 134:                 `primer'=>,'Primary (grades 1-3)',
 135:                 `element'=>,'Elementary (grades 4-6)',
 136:                 `junior'=>,'Junior High (grades 7-9)',
 137:                 `senior'=>,'Senior High (grades 10-12)',
 138:                 `college'=>,'College',
 139:                 `adult'=>`Adult'});
 140: print "\n<br>\n";
 141: print $query->submit(`Start Search'), "\n";
 142: print "Stop search after ";
 143: print $query->popup_menu(-name=>`hitlimit', -default=>`25',
 144:                          -values=>[`10','25','50','100','9999']);
 145: print " matches\n";
 146: print $query->endform, "\n";
 147: }
 148: #send the trailer
 149: sub ptrailer {
 150:   print <<EOD;
 151: <HR>
 152:   [<a href="./">Audiovisual Reference Room</a>]
 153:   <br>
 154:   [<a href="/ces.html">Cooperative Extension</a>]
 155:   [<a href="http://www.msstate.edu/">Mississippi State</a>]
 156:   [<a href="http://www.msstate.edu/web/search.htm">Search MSU's Web</a>]
 157:   <br>
 158:   <FONT SIZE="-1">
 159:   For information about this page, contact
 160:      <a href="mailto:avlibrar\@ces.msstate.edu">avlibrar\@ces.msstate.edu</a>.
 161:   <br>
 162:   <b>Last modified:</b> 08-06-96
 163:   <br>
 164:   <A HREF="http://www.msstate.edu/web/disclaim.htm">Mississippi State University
        is an equal opportunity institution.</A>
 165:   </FONT>
 166: </BODY>
 167: </HTML>
 168: EOD
 169: }

Figure 8.3. Output from Listing 8.5.

Output from Listing 8.5.

Figure 8.4. More output from Listing 8.5.

More output from Listing 8.5.