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.
Figure 8.4. More output from Listing 8.5.
by updated