Help me make this more efficient. T

[DeZiner]

Help me make this more efficient. To many or's in the select statement.
I have two search fields basically city and state. Ultimately if someone fills out both I would like to retrieve only those selected cities in that state. Right now it gets all cities in the state. But, if I take out the state part then I get an error that the form field was not specified. Any help appreciated. I can check for state on the next page, but isn't that more inefficientcy?

SELECT general.city,general.state,general.name,States.image,States.state_name,States.state_pic
				FROM general INNER JOIN States
				ON general.state=States.state_pic
				WHERE state='#form.state#' and city LIKE '%#form.search#%' or state='#form.state#' or city LIKE '%#form.search#%' or zip='#form.search#' or name LIKE '%#form.search#%'
				GROUP BY general.city, general.state,States.image,States.state_name,States.state_pic,general.name
				ORDER BY general.city,States.image,States.state_name,States.state_pic
				
		</cfquery>

DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic

 

[Glowball]

So you have this condition, right?

They pick both a state and a city
They pick just a state
They pick just a city
They pick just a zip
They pick just a name

If so, and assuming that none of these is a checkbox or radio or something that requires IsDefined() first, you could do this:

<cfset sqlStr = &quot;SELECT general.city,general.state,general.name,States.image,States.state_name,States.state_pic FROM general INNER JOIN States ON general.state=States.state_pic WHERE 0=0 &quot;>
<cfif Len(FORM.state) AND Len(FORM.city)>
    <cfset sqlStr = VARIABLES.sqlStr & &quot;AND state = '&quot; & FORM.state & &quot;' AND city LIKE '%&quot; & FORM.city & &quot;%' &quot;>
<cfelseif Len(FORM.state)>
    <cfset sqlStr = VARIABLES.sqlStr & &quot;AND state = '&quot; & FORM.state & &quot;' &quot;>
<cfelseif ...

<cfset sqlStr = VARIABLES.sqlStr & &quot;ORDER BY general.city,States.image,States.state_name,States.state_pic&quot;>
<cfquery ...>
#PreserveSingleQuotes(VARIABLES.sqlStr)#
</cfquery>

Is that what you were looking for? If not, what are the conditions that you want to search for, based on what they supply in the form? Hope I'm not misunderstanding the question...

Also, can you get the same results if you leave out the GROUP BY clause? Looks like you might.