Make sure you use single quotes around text values.
You could end up with "WHERE OR name = <name>".
I usually construct my WHERE clause outside my CFQUERY.
<CFSET whereClause = "">
<CFIF isDefined("id")>
<CFSET whereClause = whereClause & " id = #id# OR">
</CFIF>
<CFIF isDefined("name")>
<CFSET whereClause = whereClause & " name = '#name#' OR>
</CFIF>
<!--- at this point, you either have an empty string (which is ok) or some
condition(s) with " OR" at the end --->
<CFIF whereClause NEQ "">
<CFSET whereClause = " WHERE " & left(whereClause, len(whereClause)
- 3)>
</CFIF>
--------------
If you need to check multiple form fields to create your WHERE clause, you
can loop through the form.fieldnames list, check for non-empty input, then
create your where clause. You have an issue regarding the handling of
different field types: numeric, string, date, etc. You may have to handle
these differently. You can either make your form field names descriptive
(e.g., dtOpen, intID, strName), or create lists on the action page of each
type (e.g., listNumFields = "ID,<other>", listStringFields="Name,<other>"),
then loop through each of those lists, adding the single quotes around your
text fields.
-----Original Message-----
From: Dan Donnick [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 28, 2000 11:36 AM
To: CF-Talk
Subject: conditional where statement
is this correct or am i missing something?
select *
from tbl
where <cfif isdefined(")>
id = #id#
</cfif>
or
<cfif isdefined(")>
name = #name#
</cfif>
______________________________
Dan Donnick
Cold Fusion Developer
Bixler Incorporated
1250 24th Street
Suite 250
Washington, DC 20037
P: (202) 686-5800
F: (202) 686-5801
______________________________
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
