How to filter query results based on selection from dropdown list

Question

I have two tables: foodrequest and employeedetails. I am able to search the records from those tables, by date, based on a "FromDate" and "ToDate" values like this:

<cfset var qryFoodRequest = queryNew("")>       
<cfset var localData = structNew()>

<cfif (structKeyExists(Form,"Fromdatepicker") AND isDate(Form.Fromdatepicker) ) 
      AND (structKeyExists(Form,"Todatepicker") AND isDate(Form.Todatepicker) )>
    <cfset localData.frmDate = listGetAt(Form.Fromdatepicker,3,'/') & '-' & listGetAt(Form.Fromdatepicker,2,'/') & '-' & listGetAt(Form.Fromdatepicker,1,'/')>
    <cfset localData.toDate = listGetAt(Form.Todatepicker,3,'/') & '-' & listGetAt(Form.Todatepicker,2,'/') & '-' & listGetAt(Form.Todatepicker,1,'/')>
</cfif>


<cfquery name="qryFoodRequest" datasource="ebms"  cachedWithin = "#createTimeSpan(0,0,0,5)#">
    SELECT  e.FullName FullName
            , e.EmployeeCode EmployeeCode
            , e.DesignationName DesignationName
            , e.DateOfJoin DateOfJoin
            , e.DepartmentName DepartmentName
            , e.ManagerName ManagerName
            , e.ParentOffice ParentOffice
            , f.Requesttype Requesttype
            , f.Foodtype Foodtype
            , f.ReqDate ReqDate
            , f.remarks remarks
    FROM    foodrequest f INNER JOIN employeedetails e 
                 ON  f.EmployeeId = e.empId 

    <cfif structKeyExists(localData,"frmDate") AND structKeyExists(localData,"toDate")>
        AND f.ReqDate between ('#localData.frmDate#') and ('#localData.toDate#')
    </cfif>     
    order by e.empId;
</cfquery>          

<cfreturn qryFoodRequest>

But I am not able to search by employee. I.e. If I select any employee from a dropdown list, it will return their records only.

Can anybody help me on this?

Answer 1

Assuming you want to run the same code you provided that will occasionally have dates and occasionally have employee ID's and occasionally have both, then I think the below will work.

<cfquery name="qryFoodRequest" datasource="ebms"  cachedWithin = "#createTimeSpan(0,0,0,5)#">
SELECT  e.FullName FullName
        , e.EmployeeCode EmployeeCode
        , e.DesignationName DesignationName
        , e.DateOfJoin DateOfJoin
        , e.DepartmentName DepartmentName
        , e.ManagerName ManagerName
        , e.ParentOffice ParentOffice
        , f.Requesttype Requesttype
        , f.Foodtype Foodtype
        , f.ReqDate ReqDate
        , f.remarks remarks
FROM    foodrequest f INNER JOIN employeedetails e 
             ON  f.EmployeeId = e.empId 

<cfif structKeyExists(localData,"frmDate") AND structKeyExists(localData,"toDate")>
    AND f.ReqDate between (<cfqueryparam cfsqltype="cf_sql_date" value="#localData.frmDate#">) and (<cfqueryparam cfsqltype="cf_sql_date" value="#localData.toDate#">)
</cfif>
<cfif structKeyExists(form,"empId")>
    AND e.empId = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.empId#">
</cfif>
order by e.empId;
</cfquery>

You may want to add more validation to the empId field. I also updated your date parameters to use which is best practice and pretty critical to proper page security.