Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: Newbie Search Question

    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="<b>Employee ID</b>"
    width=10
    text="#Employee_ID#">
    <cfcol header="<b>First Name</b>"
    width=10
    text="#First_Name#">
    <cfcol header="<b>Last Name</b>"
    width=10
    text="#Last_Name#">
    <cfcol header="<b>Department</b>"
    width=10
    text="#Department#">
    <cfcol header="<b>District</b>"
    width=10
    text="#District#">
    <cfcol header="<b>Course Name</b>"
    width=2
    text="#Training_Desc#">
    <cfcol header="<b>Instructor</b>"
    width=10
    text="#Training_Instructor#">
    <cfcol header="<b>Date</b>"
    width=10
    text="#Training_Dt#">
    <cfcol header="<b>Course Type</b>"
    width=10
    text="#Training_Type#">
    <cfcol header="<b>Location</b>"
    width=10
    text="#Training_Location#"></cftable>
    <div align="center">
    <p><font face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular">Note: Districts are labeled by number, i.e., Boston = 01, New York = 02, etc.</font></p>
    </div>
    </body>
    </html>

  2. #2
    Join Date
    Sep 2003
    Location
    Brussel
    Posts
    52
    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.
    A good programmer is a LAZY programmer!

  3. #3
    Join Date
    Sep 2003
    Location
    Brussel
    Posts
    52
    Why is everybody using INNER JOIN ??? Someone answer that!
    A good programmer is a LAZY programmer!

  4. #4
    Join Date
    Oct 2003
    Posts
    7
    I finally got my search to work:

    <!--- Select the record --->

    <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>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •