Newbie Search Question

helios76 · #1 (**permalink**) 10-29-03, 15:09

I am trying to create a search for a database with 2 tables related by Employee_Id, so that when the user inputs a search parameter like the Location of training it will show all the results of that search. Below is what I have so far, but instead of just results for the single search parameter, I get everything in the database.

Sure would appreciate some pointers.

Thanks,
h
________________________

<cfquery name="GetEmployee" datasource="CF_CSTS_DSN" >
SELECT ('#Form#')
FROM CST_EmployeeTraining INNER JOIN CST_Employee ON CST_EmployeeTraining.Employee_ID=CST_Employee.Empl oyee_ID
WHERE 1=1
<cfif isdefined("Form.Employee_ID")> AND CST_Employee.Employee_ID='#FORM.Employee_ID#'</cfif>
<cfif isdefined("Form.First_Name")> AND CST_Employee.First_Name='#FORM.First_Name#'</cfif>
<cfif isdefined("Form.Last_Name")> AND CST_Employee. Last_Name='#FORM.Last_Name#'</cfif>
<cfif isdefined("Form.District")> AND CST_Employee. District='#FORM.District#'</cfif>
<cfif isdefined("Form.Department")> AND CST_Employee.Department='#FORM.Department#'</cfif>
<cfif isdefined("Form.Training_Desc")> AND CST_EmployeeTraining.Training_Desc='#FORM.Training _Desc#'</cfif>
<cfif isdefined("Form.Training_Instructor")> AND CST_EmployeeTraining.Training_Instructor='#FORM.Tr aining_Instructor#'</cfif>
<cfif isdefined("Form.Training_Dt")> AND CST_EmployeeTraining.Training_Dt='#FORM.Training_D t#'</cfif>
<cfif isdefined("Form.Training_Type")> AND CST_EmployeeTraining.Training_Type='#FORM.Training _Type#'</cfif>
<cfif isdefined("Form.Training_Location")> AND CST_EmployeeTraining.Training_Location='#FORM.Trai ning_Location#'</cfif>
</cfquery>
<html>
<head> <title>Data Request</title> </head>
<body>
<div align="center">
<img src="CSTshorthead.jpg" width="580" height="72" border="0">
</div>
<cftable query="GetEmployee" startrow="1" colspacing="2" htmltable colheaders>
<cfcol header="Employee ID"
width=10
text="#Employee_ID#">
<cfcol header="First Name"
width=10
text="#First_Name#">
<cfcol header="Last Name"
width=10
text="#Last_Name#">
<cfcol header="Department"
width=10
text="#Department#">
<cfcol header="District"
width=10
text="#District#">
<cfcol header="Course Name"
width=2
text="#Training_Desc#">
<cfcol header="Instructor"
width=10
text="#Training_Instructor#">
<cfcol header="Date"
width=10
text="#Training_Dt#">
<cfcol header="Course Type"
width=10
text="#Training_Type#">
<cfcol header="Location"
width=10
text="#Training_Location#"></cftable>
<div align="center">
Note: Districts are labeled by number, i.e., Boston = 01, New York = 02, etc.
</div>
</body>
</html>

Lazy · #2 (**permalink**) 10-30-03, 10:00

Thats ColdFusion, no PL/SQL, but I guess it's about the SQL-query

I created a search for a project but did it this way:

<CFIF IsDefined("[searchButtonPressed]") IS TRUE>
<CFQUERY NAME="[name]" DATASOURCE="[database]">
SELECT DISTINCT t1.[field you want to select]
FROM [table 1] t1, [table 2] t2
WHERE t1.[column] = t2.[column]
AND t1.[column] LIKE '%#Form.[what you're searching for]#%';
</CFQUERY>

<CFOUTPUT QUERY="[name]">
#[field you selected]#
</CFOUTPUT>
</CFIF>

Hope it works.

Lazy · #3 (**permalink**) 10-30-03, 10:01

Why is everybody using INNER JOIN ??? Someone answer that!

helios76 · #4 (**permalink**) 11-03-03, 09:45

I finally got my search to work:



<cfquery name="GetEmployee" datasource="CF_CSTS_DSN" >
SELECT *
FROM CST_EmployeeTraining INNER JOIN CST_Employee ON CST_EmployeeTraining.Employee_Id=CST_Employee.Empl oyee_Id
WHERE CST_Employee.Employee_ID LIKE '%#form.search#%'
OR CST_Employee.First_Name LIKE '%#form.search#%'
OR CST_Employee.Last_Name LIKE '%#form.search#%'
OR CST_Employee.District LIKE '%#form.search#%'
OR CST_Employee.Department LIKE '%#form.search#%'
OR CST_EmployeeTraining.Training_Desc LIKE '%#form.search#%'
OR CST_EmployeeTraining.Training_Dt LIKE '%#form.search#%'
OR CST_EmployeeTraining.Training_Type LIKE '%#form.search#%'
OR CST_EmployeeTraining.Training_Location LIKE '%#form.search#%'
</cfquery>

Internet Services

Web Development

Digital Marketing

Consumer Tech