|
|
The user needs to be able to select from one or more items on the page:
course_title, request_date, start_date, person's last name or first name or all
of the above.
When I select one or two of the first items the query works fine. If I skip
the first item and select the last item, it either finds nothing or processes
forever or till it times out. I know part of the problem is I'm setting the
value of the first item, course_title (if the person does not select it) to '
'. There are no null items in the course_title column. But how do I write the
query so it will skip this one if the person wants and just search the
remaining items.
I attempted adding IsDefined around each query item if the form sends it but
I'm still not sure how to write the "and/or" part. Where does that go in the
cfif statement if a person only selects one item?
Thanks for the help
<cfquery datasource="train_papaya" name="search">
select a.request_date,
a.start_date,a.name,a.course_code,a.course_title,a.app_disapp_date,a.supv_id,a.r
eason,b.display
from register a, training_reasons b
where a.reason = b.id and
<!--- <cfif IsDefined("form.course")> --->
a.course_code = '#form.coursecode#' and
<!--- </cfif>
<cfif IsDefined("form.requestdate")> --->
a.request_date #form.op#
'#DateFormat(form.requestdate,"mm/dd/yyyy")#' and
<!--- </cfif>
<cfif IsDefined("form.approvaldate")> --->
a.app_disapp_date #form.op2#
'#DateFormat(form.approvaldate,
"mm/dd/yyyy")#' and
<!--- </cfif>
<cfif IsDefined("form.startdate")> --->
a.start_date #form.op3# '#DateFormat(form.startdate,
"mm/dd/yyyy")#' and
<!--- </cfif>
<cfif IsDefined("form.lastname")> --->
a.name Like '%#form.lastname#%' and
<!--- </cfif>
<cfif IsDefined("form.firstname")> --->
a.name like '%#form.firstname#%'
<!--- </cfif> --->
Order by name
</cfquery>
|
|