Maybe something like: <cfset searchfields = "photoname,filename,photographer">
<cfquery datasource="#db#" name="session.searchresults" > select * from something where 1=1 <cfloop list="form.searchterms" index="s" delimiters="+, " <cfloop list="searchfields" index="f"> OR #i# LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#s#%"> </cfloop> </cfloop> </cfquery> BTW, if you have a large database, and your site is at all successful, then putting the search results into the session will most likely take down coldfusion. On 03/11/06, Patrick Forsythe <[EMAIL PROTECTED]> wrote: > > here is the problem i am having, I have a search box where a user can > enter in something to search for, based on what the user enters in I > build a query and execute it thusly. > > <cfquery datasource="#db#" name="session.searchresults" > > #preservesinglequotes(querystring)# > </cfquery> > > query string is put together like this > <cfset querystring = #querystring# & " AND (photoname LIKE > '%#form.searchterms#%' OR filename LIKE '%#form.searchterms#%' OR actors > LIKE '%#form.searchterms#%' OR movie LIKE '%#form.searchterms#%' OR > foldertitle LIKE '%#form.searchterms#%' OR descr LIKE > '%#form.searchterms#%' OR photographer LIKE '%#form.searchterms#%' OR > boxnum LIKE '%#form.searchterms#%' OR foldernum LIKE > '%#form.searchterms#' OR imagenum LIKE '%#form.searchterms#%' OR > keywords LIKE '%#form.searchterms#%' OR title LIKE > '%#form.searchterms#%') OR location LIKE '%#form.searchterms#%' "> > > > The problem is anytime you search for soemthingthat has a single quote > in it like say rob o'flanery it will throw an error i tried adding > escape '/' at the end of the querystring decleration but still no dice > throws an error still with or with out the / infront of the '. > > Is there something I am doing wrong? what is the right way to do this? > > > new part --- > > It was suggested that I do this using cfqueryparam but I am not sure how > I should go a bout this, I am creating all of this at runtime based on > userinput so whould I add the cfqueryparam to the string I am building? > or use some conditionals in the cfquery tag to set these queryparams? > thanks again for all the help. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259098 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
