Copy link to clipboard
Copied
I am trying to build a query where the end-user can search a field in the table for multiple keywords. I've tried several examples I found by searching SQL forums yet I keep getting Incorrect syntax near 'keyword*'. Does CF do it differently?
Something Like this....
<cfif isdefined("form.search1")>
<cfquery name="Messages" datasource="somedatasource">
Select *
From SomeTable
Where AffiliateID = #Session.AffiliateID# AND Contains(messagefile, '#Form.Search1#*')
<cfif isdefined("form.search2")>
AND Contains(messagefile, '#Form.Search2#*')
</cfif>
<cfif isdefined("form.search3")>
AND Contains(messagefile, '#Form.Search3#*')
</cfif>
Order by SomeField
</cfquery>
</cfif>
Greatly appreciate any help...
Copy link to clipboard
Copied
Basically you've already answered your question, but it's not Contains() that goes in the WHERE clause.
Also, I would advise to not use SELECT *; enter all the column names that you want to access. This will be much more efficient. SELECT * forces the database to reference a system table to get the column names; and unless you really do need every column, it's fetching data that takes up bandwidth that you're going to just throw away.
Depending upon what flavour of SQL you're using, the WHERE clause should be something like:
WHERE affiliateID = <cfqueryparam value="#session.affiliateID#" />
AND messagefile = <cfqueryparam value="#form.search1" />
<cfif StructKeyExists(form,"search2")>
AND messagefile = <cfqueryparam value="#form.search2#" />
</cfif>
HTH,
^ _ ^
Copy link to clipboard
Copied
I replied to this message a while ago, and my suggestion is not showing. WTF??
Copy link to clipboard
Copied
I replied to your question, but it seems to have disappeared.
Depending upon what flavour of SQL you are using, the where clause should be:
WHERE messagefile = <cfqueryparam value="#form.search1#" />
This is assuming that 'messagefile' is a text or integer datatype, and not a BLOB/CLOB as the 'file' part of the name suggests.
HTH,
^ _ ^
Copy link to clipboard
Copied
Copy link to clipboard
Copied
Copy link to clipboard
Copied
Copy link to clipboard
Copied
Copy link to clipboard
Copied
This is really not a coldfusion problem. Indeed, it's not one well served by "normal sql". Instead, you want to look to your database (sql server? mysql? oracle?) for any provision it may have for "full text search" or the like. That would allow you to more easily "find any word or phrase" within some column or columns of data. Then once you find how that DB supports that feature, THEN (if it's based on SQL, or a stored procedure call) you can put it into CF to process it.
Hope that's helpful. If not, let us know what more you may need.
Copy link to clipboard
Copied
Copy link to clipboard
Copied
Copy link to clipboard
Copied
AH! Okay.. it _IS_ the filename. (BTW.. you should replace all spaces in filenames with either underscore or %20.)
<cfquery name="Messages" datasource="somedatasource">
Select ID, MessageUID, MessageFile, AffiliateID, SerialGroup, Size, LastUsed
From SomeTable
Where AffiliateID = <cfqueryparam value="#Session.AffiliateID#" />
AND messagefile LIKE <cfqueryparam value="%#trim(form.search1)#%" />
<cfif isdefined("form.search2")>
AND messagefile LIKE <cfqueryparam value="%#trim(form.search2)#%" />
</cfif>
<cfif isdefined("form.search3")>
AND messagefile LIKE <cfqueryparam value="%#trim(form.search3)#%" />
</cfif>
Order by SomeField
</cfquery>
HTH,
^ _ ^
UPDATE: If you are using CF10 or later, be sure to pass all search keywords through canonicalize() to prevent SQL injection.
Copy link to clipboard
Copied
Copy link to clipboard
Copied
Copy link to clipboard
Copied