Well actually if you were to put in an cfif statement (which you are right about) it should be <cfif form.search is not "">. It form.search always be defined if you are coming from the form. As I mention the problem seems to be using the % wildcard. It works fine without it but the user can't be as vague in what they type. It must be a valid first name or last name to pull back a record. Does anyone have any other suggestions?
Thanks, Rob <CFQUERY NAME="getContact" DATASOURCE="#DSN#"> SELECT contacts.contactID, contacts.firstName, contacts.lastName, company.companyName FROM contacts, company WHERE company.companyID = contacts.companyID <cfif form.search is not ""> AND (contacts.firstName LIKE '#trim(search)#') OR (contacts.lastName LIKE '#trim(search)#') </cfif> ORDER BY contacts.lastName, contacts.firstName </CFQUERY> -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Karl Zarudny Sent: March 5, 2002 5:12 PM To: [EMAIL PROTECTED] Subject: Re: [CFTALKTor] SQL question? Sorry I read that wrong. Wouldn't you simply wrap an IF around the items after the company.companyid = contacts.contactid? i.e. <CFQUERY NAME="getContact" DATASOURCE="#DSN#"> SELECT contacts.contactID, contacts.firstName, contacts.lastName, company.companyName FROM contacts, company WHERE company.companyID = contacts.companyID <cfif IsDefined("FORM.search")> AND (contacts.firstName LIKE '#trim(Form.search)#%' OR contacts.lastName LIKE '#trim(Form.search)#%') </cfif> ORDER BY contacts.lastName, contacts.firstName </CFQUERY> > From: Karl Zarudny <[EMAIL PROTECTED]> > Reply-To: [EMAIL PROTECTED] > Date: Tue, 05 Mar 2002 17:06:14 -0500 > To: <[EMAIL PROTECTED]> > Subject: Re: [CFTALKTor] SQL question? > > I may be mistaken but shouldn't there be brackets around the OR clause? > > <CFQUERY NAME="getContact" DATASOURCE="#DSN#"> > SELECT contacts.contactID, contacts.firstName, contacts.lastName, > company.companyName > FROM contacts, company > WHERE company.companyID = contacts.companyID > AND (contacts.firstName LIKE '#trim(Form.search)#%' > OR contacts.lastName LIKE '#trim(Form.search)#%') > ORDER BY contacts.lastName, contacts.firstName > </CFQUERY> > > >> From: "Rob Matlow" <[EMAIL PROTECTED]> >> Reply-To: [EMAIL PROTECTED] >> Date: Tue, 5 Mar 2002 16:51:47 -0500 >> To: "CFTALK" <[EMAIL PROTECTED]> >> Subject: [CFTALKTor] SQL question? >> >> >> I am banging my head trying to get this simple search interface to work and >> I'm just not getting back what I expect. I have a textbox where the user >> can type in a person's name (contact) first or last and it should search the >> firstName field and lastName field in the database. When I search on "all" >> it returns the proper contacts but associated with ever company entered. >> When I fill in something it returns the correct information. Any ideas? >> Hope this made sense. >> >> >> <CFQUERY NAME="getContact" DATASOURCE="#DSN#"> >> SELECT contacts.contactID, contacts.firstName, contacts.lastName, >> company.companyName >> FROM contacts, company >> WHERE company.companyID = contacts.companyID >> AND contacts.firstName LIKE '#trim(Form.search)#%' >> OR contacts.lastName LIKE '#trim(Form.search)#%' >> ORDER BY contacts.lastName, contacts.firstName >> </CFQUERY> >> >> >> Thanks, >> >> Rob >> >> >> - >> You are subscribed to the CFUGToronto CFTALK ListSRV. >> This message has been posted by: "Rob Matlow" <[EMAIL PROTECTED]> >> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ >> Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ >> This System has been donated by Infopreneur, Inc. >> (http://www.infopreneur.net) > > - > You are subscribed to the CFUGToronto CFTALK ListSRV. > This message has been posted by: Karl Zarudny <[EMAIL PROTECTED]> > To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ > Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ > This System has been donated by Infopreneur, Inc. > (http://www.infopreneur.net) - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: Karl Zarudny <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net) - You are subscribed to the CFUGToronto CFTALK ListSRV. This message has been posted by: "Rob Matlow" <[EMAIL PROTECTED]> To Unsubscribe, Please Visit and Login to http://www.CFUGToronto.org/ Manager: Kevin Towes ([EMAIL PROTECTED]) http://www.CFUGToronto.org/ This System has been donated by Infopreneur, Inc. (http://www.infopreneur.net)
