Hi, I am trying to build a search page in which people can query our MySQL database based upon criteria chosen on the search page. The three criteria I am using are City, State, and Zipcode. I have created the cfquery code which can be seen below. The problem I am having is that if none of those 3 criteria are chosen, and the form is submitted, the results will include ALL rows in the table as opposed to NONE.
This is probably due to the last entry "0=0". I have included 0=0 because if state is defined with data but zipcode and city are not, then the select query will include an AND statement at the end (where table.state LIKE California ***AND*** ) This AND needs to be closed out regardless of what items are chosen. My example would close it out as (AND 0=0). Without using the cfinput attribute "REQUIRED" (which will solve my problem) how would i go about structuring my select query properly? THANKS for any input!!! <cfquery name="cfdb" datasource="#DSN#"> SELECT * FROM table where <cfif #form.state# is not ""> table.state LIKE '#form.state#' AND</cfif> <cfif #form.zipcode# is not ""> table.zipcode LIKE '#form.zipcode#' AND</cfif> <cfif #form.city# is not ""> table.city LIKE '#form.city#' AND</cfif> 0=0 </cfquery> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302945 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
