Start from the beginning, you need a basic statement like:
SELECT Columns
FROM Table
WHERE Condition
AND Condition
OR Condition
Your statement is likely to look like:
SELECT Columns
FROM Table
AND Condition
AND Condition
AND Condition
AND Condition
WHERE Condition
WHERE Condition
WHERE Condition
WHERE Condition
WHERE should start your conditions, then AND's and OR's to separate the
rest.
With your example you will have the problem of not knowing when to use
WHERE, a way around this is to have something like:
SELECT Columns
FROM Table
WHERE 1 = 1
AND Condition
AND Condition
AND Condition
That way you remove the need to work out when the first condition is met and
when to use the WHERE.
So to wrap it all up:
<cfquery name="contacts" datasource="cdb">
SELECT last_name, city, county, neighborhood,
hockey_fan, soccer_fan, baseball_fan, basketball_fan
FROM Contacts
WHERE 1 = 1
<cfif last_name IS NOT "">
AND last_name LIKE '#last_name#'
</cfif>
<cfifcity IS NOT "">
AND city LIKE '#city#'
</cfif>
<cfif county IS NOT "">
AND county LIKE '#county#'
</cfif>
<cfif neighborhood IS NOT "">
AND neighborhood LIKE '#neighborhood#'
</cfif>
<cfif hockey_fan IS NOT "">
AND hockey_fan = 'YES'
</cfif>
<cfif soccer_fan IS NOT "">
AND soccer_fan = 'YES'
</cfif>
<cfif baseball_fan IS NOT "">
AND baseball_fan = 'YES'
</cfif>
<cfif hockey_fan IS NOT "">
AND hockey_fan = 'YES'
</cfif>
ORDER BY last_name
</cfquery >
Be sure to check the boundary cases where none of the extra conditions are
added and where all of the conditions are added.
Adrian
-----Original Message-----
From: Kent A. Orso [mailto:[EMAIL PROTECTED]
Sent: 18 March 2006 17:46
To: CF-Newbie
Subject: RE: CFQuery from Database
After doing the changes below, I receive the folloiwng error when I enter a
last name and a value of yes for baseball_fan.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Syntax error (missing operator) in query expression
'last_name LIKE 'smith' baseball_fan = 'yes''.
________________________________
From: Nathan Chen [mailto:[EMAIL PROTECTED]
Sent: Fri 3/17/2006 4:38 PM
To: CF-Newbie
Subject: RE: CFQuery from Database
You have a syntax error right after your "From Contacts". If last_name
is not null, then you'll have a problem because there is no "where" in
that statement.
What you should have is:
>From Contacts
Where
<CFIF last_name IS NOT ""> last_name LIKE '#last_name#'</cfif>
(notice I took out "AND" here)
Then, take out the four "where" after <cfif hockey_fan IS NOT "">
Nathan Chen
-----Original Message-----
From: Kent A. Orso [mailto:[EMAIL PROTECTED]
Sent: Friday, March 17, 2006 2:28 PM
To: CF-Newbie
Subject: CFQuery from Database
I'm trying to write a query that returns results on a database query
after a user enters any information for a contact by entering any of the
following in the form page:
Last Name
City
County
Neighborhood
hockey_fan (Select from List Yes/No)
Soccer_fan (Select from List Yes/No)
Baseball_Fan (Select from List Yes/No)
Basketball_Fan (Select from List Yes/No)
The code that is generating errors:
<cfquery name="contacts" datasource="cdb">
SELECT last_name, city, county, neighborhood, hockey_fan, soccer_fan,
baseball_fan, basketball_fan
FROM Contacts
<CFIF last_name IS NOT ""> AND last_name LIKE '#last_name#'
</CFIF>
<cfif city IS NOT ""> AND city LIKE '#city#'
</cfif>
<cfif county IS NOT ""> AND county LIKE '#county#'
</cfif>
<cfif neighborhood IS NOT ""> AND neighborhood LIKE '#neighborhood#'
</cfif>
<cfif hockey_fan IS NOT ""> WHERE hockey_fan = 'YES'
</cfif>
<cfif soccer_fan IS NOT ""> WHERE soccer_fan = 'YES'
</cfif>
<cfif baseball_fan IS NOT ""> WHERE baseball_fan = 'YES'
</cfif>
<cfif hockey_fan IS NOT ""> WHERE hockey_fan = 'YES'
</cfif>
ORDER BY last_name
</CFQUERY>
I'm not sure why I can't seem to get a return when the user enters a
last name of Smith, and YES to Hockey Fan and Yes to Baseball fan.
Thanks for any help in advance,
kent
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:15:1720
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/15
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:15
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
