Personnel Directory (Behind the Scenes)

[ << Back to Example ]

Overview

The Personnel Directory example demonstrates a basic search for individuals listed in an employee directory (the Employees table of the examples database). Users can enter a first and/or last name to search for, and all entries which fully or partially match the search criteria are returned.

The key technique demonstrated by this example is the use of the SQL LIKE operator to do 'pattern-matched' searching of text fields. The LIKE operator instructs the database to retrieve entries which are similar to the criteria entered (rather than limiting the search to exact matches).


Implementation Files

Directory Name: DIRECT
File Name       Type       Function                         

MAIN.HTM        HTML       Allows the user to specify the   
                           criteria used to search for      
                           individuals in the personnel     
                           directory.                       

RESULTS.DBM     Template   Displays the list of             
                           individuals matching the search  
                           criteria specified in MAIN.HTM.  

MAIN.HTM

<HTML>

<HEAD>
<TITLE>Personnel Directory Example</TITLE>
</HEAD>

<BODY>

<H2>Personnel Directory Example</H2>
<a href="details.htm"><img src="../how.gif" border=0 align=middle></a> 
<a href="details.htm">Behind the Scenes</a>
<HR>
<P>

<H3>Acme, Inc. Personnel Directory</H3>

<!-- The form's action calls Cold Fusion and instructs it 
     to process the query using the 
     /cfstand/examples/direct/results.dbm template file. This 
     template file will display information on the employees 
     which match the search criteria. -->

<FORM ACTION="/~wsapi/cfusion?amp;Template=/cfstand/examples/direct/results.dbm" METHOD="POST">

<P>Enter the first and/or last name of the employee
you want to find:
<P>

First Name: 
<INPUT NAME="FirstName" SIZE=20>   <BR>

Last Name:  
<INPUT NAME="LastName" SIZE=20>   <BR>

<P>
<INPUT TYPE="submit" VALUE="  Submit Search  "> <INPUT TYPE="reset" 
                                                VALUE="Clear Entries">

<P><HR><P>Search suggestions for example employee data:

<UL>
<LI>For <I>First Name</I> searches, try 'Peter', 'Erica', and 'D'. <P>
<LI>For <I>Last Name</I> searches, try 'Smith', 'Leary', and 'J'. <P>
</UL>

</FORM>

<P>
<HR>
[ <a href="../examples.htm">Example Applications</a> ]
[ <a href="../../getstart.htm">Getting Started with Cold Fusion</a> ]

</BODY>
</HTML>


RESULTS.DBM


<!-- Use a DBQUERY to find all of the records which have
     a first and/or last name which is LIKE the ones
     entered by the user. LIKE is a special SQL operator
     which does 'pattern-matched' searches. In this 
     search we use LIKE with the % wildcard character 
     (simillar to * wildcard character in DOS) -->

<DBQUERY NAME="Employees" DATASOURCE="CF Examples"
    SQL = "SELECT * FROM Employees 
             WHERE FirstName LIKE '#Form.FirstName#%' AND
                   LastName LIKE '#Form.LastName#%' " >

<HTML>
<HEAD>
<TITLE>Personnel Directory Search Results</TITLE>
</HEAD>

<BODY>

<H2>Personnel Directory Search Results</H2>
<HR>


<!-- Use a DBOUTPUT to present the list of employees which 
     matched the user's search criteria.  -->

<DBOUTPUT QUERY="Employees">
<P>

<H3>#FirstName# #LastName#</H3>

<PRE>
Department:  #Department# 
Phone:       #Phone#
E-Mail:      #EMail# 
</PRE>

<HR>

</DBOUTPUT>


[ <a href="/cfstand/examples/direct/main.htm">
     Personnel Directory Example</a> ]
[ <a href="/cfstand/examples/examples.htm">Example Applications</a> ]

</BODY>

</HTML>


[ Personnel Directory Example ] [ Example Applications ]