Happy to help.... if only it didn't take me a bazillion tries to type what I meant. Thanks for your patience - lack of sleep and coding don't seem to mix well for me :-)
Karl On Tuesday, March 5, 2002, at 09:10 PM, Rob Matlow wrote: > You got it Karl! The brackets were wrong. Thanks a lot. Too much > syntax to > remember! > > Rob > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of Karl Zarudny > Sent: March 5, 2002 6:54 PM > To: [EMAIL PROTECTED] > Subject: Re: [CFTALKTor] SQL question? > > > yeah, it's ben a long day - that's what I meant.... not enough sleep > lately. Do you mean that when you enter text in the search box, you get > back a record for each user * the number of companies? > > ie > John Smith Company 1 > John Doe Company 1 > Jonh Hancock Company 1 > John Smith Company 2 > John Doe Company 2 > Jonh Hancock Company 2 > John Smith Company 3 > John Doe Company 3 > Jonh Hancock Company 3 > > So let me see if I've got this straight - when you don't enter anything > (to do an "all") search it returns all the contact names but associated > with every company in your company table, as shown above. But when you > actually enter search criteria, such as "John" for example, the search > returns properly? Are you only trying to find one contact at a time or > are you trying to find multiple contacts? As I'm looking at the sql > right now it seems like you should be, at worst, getting back a > duplicate set not one for each company. And it still seems like the > brackets are in the wrong place.... > > Should be: > AND (contacts.firstName LIKE '#trim(search)#' OR contacts.lastName LIKE > '#trim(search)#') > > Not: > AND (contacts.firstName LIKE '#trim(search)#') > OR (contacts.lastName LIKE '#trim(search)#') > > Like this it seems to me, and I could be wrong, that it's no different > than not having the brackets at all. Unfortunately I'm not at my testing > box right now so I can't really replicate your error. I'm left rolling > it over in my sleep-lacking head. > > BTW I'm not as dim as I sound right now - I'll figure it out > eventually :-) Anyone else have any thoughts? > > Karl > > > > On Tuesday, March 5, 2002, at 06:06 PM, Rob Matlow wrote: > >> 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) >> > > - > 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) > - 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)
