SQL query with multiple contains question.

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.

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.

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,

^ _ ^

I replied to this message a while ago, and my suggestion is not showing.  WTF??

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,

^ _ ^