Sorting data based on mutliple fields

[Not A Guru]

Hello!

Ok, so I have 6 possible form text fields the user can enter data in,
corresponding to what they want to sort by. They hit 'submit', then I execute
an SQL <cfquery> in order to find all records that match the criteria entered
in the form.
Problem is, if they dont enter some data in some fields, the query looks for a
field with data in it, as opposed to by passing it and not including it as part
of the sort. What I want to be able to do is: say I want to find all records
based on three criterea, I enter that data in, and leave the remaining three,
and it finds all records matching my three criterea, ignoring the other three
that were left blank.

Below is my SQL:

<cfquery name="list" datasource="hhs">
Select *
From Marketing
WHERE (enquiry_type='#form.enquiry_type#'
AND state='#form.state#'
AND country='#form.country#'
AND method='#form.method#'
AND method='#form.sendcopy#'
AND method='#form.editorial#'
AND method='#form.they_find#'
AND method='#form.samples#'
AND prefer='#form.prefer#')
</cfquery>

How do I modify that query, such that, if for instance, no data is entered
into any fields, it displays all records in the table, instead of only finding
records where in every field, it has a NULL value (nothing entered in the
field). This is my dilemma.

Please help

Thanks buddies

Not a Guru

[bill_doe]

I'm not a gura either, but check out the code I posted under the "ORA-00911:
invalid character -runs fine in SQLPlus" topic. I used cfif statements to
include or not include certain form elements based on whether or not they were
left blank. Don't forget the cfparam's!

Hope this helps,

Bill

[BigDMorris]

You write it like this:

<cfquery name="list" datasource="hhs">
Select *
From Marketing
WHERE (enquiry_type='#form.enquiry_type#'

<CFIF FORM.State IS NOT "">
AND state='#form.state#'
</CFIF>
<CFIF FORM.Country IS NOT "">
AND country='#form.country#'
</CFIF>
etc.
etc.
etc.

Dave Morris