fulltext figured

So to give my 2 readers an update, I figured out the issue regarding my fulltext search.  Basically, it’s a ‘feature’ of cfqueryparam that I’ve either never noticed or, well, probably just never noticed.  I’ve always known that cfqueryparam does a great job of setting up your variable into its proper format, for varchar it encloses everything in single quotes and usually formats single quotes within the text as necessary.  This bombs fulltext in this particular case.

The fun thing about queries between mysql and Coldfusion is that it’s important to remember to cfqueryparam.  In every situation.  It will prevent some bad things from happening.  The trick is, you have to create your sql in a good way, or the query string will become, for a lack of better word, bad.   So here’s what I was doing.  I was creating a fulltext match statement dynamically for the three different searches, Any, ALL and Phrase

ANY: <cfset matcher= “‘#replace(stemmedKeywords,” “,”,”,”ALL”)#’ IN BOOLEAN MODE”>
ALL: <cfset matcher= “‘+#replace(stemmedKeywords, “,”, ” +”,”ALL”)#’ IN BOOLEAN MODE”>
PHRASE: <cfset matcher= “‘#chr(34)##keywords##chr(34)#’ IN BOOLEAN MODE”>

and then in my cfquery/cfqueryparam
AND MATCH (somecolumn1,somecolumn2,somecolumn3) AGAINST (<cfqueryparam cfsqltype=”cf_sql_varchar” value=”#matcher#” > )

So I’ve just had to make a small adjustment to how I set up the keywords and match/against:

ANY: <cfset matcher= “#replace(stemmedKeywords,” “,”,”,”ALL”)#”>
ALL: <cfset matcher= “+#replace(stemmedKeywords, “,”, ” +”,”ALL”)#”>
PHRASE: <cfset matcher= “#chr(34)##keywords##chr(34)#”>

and then in my cfquery/cfqueryparam
AND MATCH (somecolumn1,somecolumn2,somecolumn3) AGAINST (<cfqueryparam cfsqltype=”cf_sql_varchar” value=”#matcher#”>IN BOOLEAN MODE)

Seems simple, right?  Don’t know why I didn’t think about it earlier.  More than likely it had something to do with the fact that I was building these keywords dynamically, and I didn’t use IN BOOLEAN MODE in all of them, but after looking over the code again, I guess I do.  On a side note, the boolean search is set because of the fact that I’m looking for partial words via the stem function, which puts a * at the end of each ‘matcher’ word.

 So now I know.  And that helps you know.