3 field search not working

[braulio!]

I have a 3 field search
[url]http://www.websitemedia.net/demo/7665/without_fl/qualitymotorsdemo.cfm[/url] The 3
fields work great individually. But i am having a lot of trouble searching if
2 fields are picked or 3 fields are picked. Below is my code. I have tried
adding a cfelse but it still fails to work. Am i mising something? Please
look the code over and let me if i am missing something very obvious. Thanks
in advancd and greatly appreciated, Braulio.

SELECT stock, make, price, description, image, qualitymotorsdemo.page
FROM qualitymotorsdemo
<cfif trim(#FORM.select#) neq "all">
WHERE make = '#FORM.select#'
</cfif>
<cfif trim(#FORM.year#) neq "all">
WHERE year = '#FORM.year#'
</cfif>
<cfif trim(#FORM.model#) neq "all">
WHERE model = '#FORM.model#'
</cfif>

[Kronin555]

SELECT stock, make, price, description, image, qualitymotorsdemo.page
FROM qualitymotorsdemo
WHERE 1 = 1
<cfif trim(#FORM.select#) neq "all">
AND make = '#FORM.select#'
</cfif>
<cfif trim(#FORM.year#) neq "all">
AND year = '#FORM.year#'
</cfif>
<cfif trim(#FORM.model#) neq "all">
AND model = '#FORM.model#'
</cfif>

the 1 = 1 is needed, so don't take it out.

[braulio!]

OK great it worked. But what is the logic behind the "where 1 =1". I looked
for a way to do this in my cf mx7 web appication consruction kit reference book
but no dice. Thanks but please explain so that i can understand it. Braulio.

[braulio!]

I have also been trying to make the drop down menu display the value selected
but it always "All". I have looked at the select statement but have been
unable to set it up to display the value selected by the visitor. Any input on
this will be greatly appreciated, Braulio.

[Kronin555]

the logic behind the 1 = 1... ok.

in order to simplify things, you need a dummy where test that always equals
true (in the case where you're ANDing everything together), or always equals
false (in the case where you're ORing everything together). That way in your if
statements you can ignore the concern of whether the WHERE clause has been
started or not.

Here's 2 examples, one with the 1=1, and one without:

SELECT * FROM foo
WHERE
testCol = 'test'

SELECT * FROM foo
WHERE 1 = 1
AND testCol = 'test'

they both match the same columns. you're essentially putting a dummy "true" in
your AND list of checks. because it's "true", it doesn't have any impact on the
results. The reason you put it in there is so when you wrap the "testCol =
'test'" in a <cfif>, you have to wonder whether you need to put an AND at the
front or not. with the dummy 1 = 1 in there, you know you always put an AND in
the front of the clause, and it will always result in a valid query. Also, if
you take out the "AND testCol = 'test'" in the second query, you get this:

SELECT * FROM foo
WHERE 1 = 1

which returns the exact same information as this:

SELECT * FROM foo

it just simplifies tacking on more tests to the WHERE clause.

[braulio!]

Thank you very much for teaching me how to fish. I appreciate it, Braulio.