CSCI 4230
Internet Applicaiton Development
Fall 2000
Suugested Solution to Homework #9

(1)    For example:

<html>
<head>
<!--  h9.cfm  -->
<title>Homework #9, CSCI 4230, Fall 2000</title>
<style>
h2, h3 { color:#3333ff;
     font-family: Arial
}
body {
   font-family:bookman old style, times new roman, times;
}
</style>
</head>

<body bgcolor="#ccccff">
<h2>Faculty Directory</h2>
This is the faculty directory of School of NAS (Cold Fusion Version).

<!---  Global porting variables --->
<cfset DSN = "yue">

<!--- Other variables --->
<cfset selfURL = "http://" & CGI.SERVER_NAME & CGI.PATH_INFO >

<!---   Set SQL condition  --->
<cfset cond="">
<cfif IsDefined("URL.key")>
 <cfset key = URL.key>
 <cfoutput>
 <h3>Search Result for last name with substring
 <span style="color:green">#key#</span>:
 </h3>
 </cfoutput>
 <cfquery name="resultQuery" datasource="#DSN#">
 select LastName, FirstName, URL, Program
 from f00h9
 where LastName like '%#key#%' order by LastName
 </cfquery>
    <cfset cond = 1>
<cfelseif IsDefined("URL.fchar")>
 <cfset fchar = URL.fchar>
 <cfoutput>
 <h3>Search Result for last name with first character
 <span style="color:green">#fchar#</span>:
 </h3>
 </cfoutput>
 <cfquery name="resultQuery" datasource="#DSN#">
 select LastName, FirstName, URL, Program
 from f00h9
 where LastName like '#fchar#%' order by LastName
 </cfquery>
    <cfset cond = 1>
<cfelseif IsDefined("URL.program")>
 <cfset program = URL.program>
 <cfoutput>
 <h3>Search Result for the program
 <span style="color:green">#program#</span>:
 </h3>
 </cfoutput>
 <cfquery name="resultQuery" datasource="#DSN#">
 select LastName, FirstName, URL, Program
 from f00h9
    where Program = '#program#' order by LastName
 </cfquery>
    <cfset cond = 1>
<cfelse>
    Please use the following form to search.
</cfif>

<!---   Print out result --->
<cfif cond is not "">
 <cfset result = "">
 <cfset count = 0>
 <cfloop query="resultQuery">
     <cfset result = result & "<a href='" & resultQuery.URL &
             "'>" & resultQuery.FirstName & " " &
       resultQuery.LastName & "</a>: " &
       resultQuery.Program & "<br>">
  <cfset count = count + 1>
 </cfloop>
 <cfif count is 0>
  Sorry, there is no match.  Please try again.
 <cfelseif count is 1>
  There is 1 match.
  <p>
 <cfelse>
  There are <cfoutput>#count#</cfoutput> matches.
  <p>
 </cfif>
 <cfoutput>#result#</cfoutput>
</cfif>

<!---  show search form --->
<p>
<hr>
<h3>Search Form</h3>

<!---  Search by keyword --->
<form method="get">
Last name: &nbsp;&nbsp;
<input type="text" size="20" maxlength="50" name="key">
&nbsp;&nbsp;&nbsp;&nbsp;
<input type="submit" value="Search Last Name!">
</form>

<!--  Search by first char of last name  -->
<p>
Select the first character of the last name:
<p>
<cfloop index="ch" list="A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z">
   <cfoutput><a href="#selfURL#?fchar=#ch#">#ch#</a>&nbsp;&nbsp;</cfoutput>
</cfloop>
<p>

<!---  Program name --->
<cfquery name="progQuery" datasource="#DSN#">
select distinct program from f00h9
</cfquery>

<form method="get">
Select programs: &nbsp;
<select name="program">
<cfloop query="progQuery">
 <cfoutput><option value="#progQuery.program#">#progQuery.program#
 </cfoutput>
</cfloop>
</select>
&nbsp;&nbsp;&nbsp;&nbsp;
<input type=submit value="Search Program!">
</form>
</body>
</html>