Creating an Ajax mySQL Full Text Search in cfWheels

I’m quite a fan of mySQL full text search – don’t get me wrong, it’s not exactly Google, and it has some annoying limitations, but for a quick and easy search function, it’s not half bad. It’s particularly useful for intranets, or just listings in admin section when you might have to delve through a few thousand records otherwise.

This example uses cfWheels, and most importantly, the cfWheels plugin Remote Form Helper.

Step One: Set up an index on your Table in MySQL.

Note: you can only do this on myISAM tables, not innodb.
I usually do this is navicat, where it’s very painless to do indeed. (simply right click a table, select Design table, then select indexes: give the index a name, and select the columns in the table you want to search: make sure the index type is set to fulltext).

For the ‘navicat-less’, try something like this:

CREATE TABLE `articles` 
  `id` int(11) NOT NULL default '0',
  `title` varchar(125) default NULL,
  `topic` varchar(25) NOT NULL default '',
  `author` varchar(25) NOT NULL default '',
  `createdAt` datetime NOT NULL default '0000-00-00 00:00:00',
  `body` text NOT NULL,

Then add the index:

ALTER TABLE articles ADD FULLTEXT(title,body);

The attributes in the Fulltext() function are the column names we wish to search (I believe there’s a limit of 16 columns).

In your mySQL conf file, add this line under the [mysqld] section:

This sets mySQL to search for words of 3 characters or more, rather than the annoying default of 4.

Step 2: cfWheels setup
Download and install/activate/configure the Remote Form Helper plugin.
Make sure you remember to add the line:

addFormat(extension="js", mimeType="text/javascript");

To your config/settings.cfm file as mentioned in the plugin documentation, and also include the wheel.jquery.js file distributed with the plugin. (oh and jQuery itself, obviously).

Step 3: Search Views & Controllers

We need to create at least three files.

Firstly, our controller, /controllers/Search.cfc:

<cfcomponent extends="controller">
<cffunction name="init">
// This provides bit is essential!
 provides("html, json, js"); 

<cffunction name="q" hint="The Main Search Router">
// If search terms is incoming, and is an ajax request
if(structkeyexists(params, 's') AND len(params.s) GTE 3 AND isAjax())
// searchArticles() function returns our HTML directly to the data attribute 
renderWith(data=searchArticles(params.s), template="articles");
{ renderNothing() };

<cffunction name="searchArticles" access="private" hint="Our FullText Search">
 <cfargument name="s" required="yes" type="string" hint="The Search Term">
 <cfquery name="q" datasource="#application.wheels.datasourcename#" maxrows="50">
 SELECT id, title, topic, author, body, createdAt FROM articles
 WHERE MATCH (title,body) AGAINST (<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.s#">);
 <cfloop query="q">
  <cfset q.content[currentrow]=formatResult(string=content, highlightT=arguments.s)>
 <cfreturn q />

<cffunction name="formatResult" access="private" returntype="string" hint="Returns a shortened teaser of the main content, with highlighted search terms">
 <cfargument name="string" type="string" hint="The String to truncate">
 <cfargument name="highlightT" type="string" hint="The term to highlight">
  <cfset var newString="">
  <cfset newString=highlight(text=truncate(stripTags(arguments.string), 400), phrases=arguments.highlightT)>
  <cfreturn newString />

Next, some view files:

/views/search/index.cfm (where our search form and results output will live)

<cfparam name="params.s" default="">       
<!--- NOTE THE remote=true attribute!--->
#startFormTag(controller="search", action="q", id="advancedSearchForm", remote="true")#
#textFieldTag(name="s", placeholder="Search..", label="Search For", value=params.s)#
<div id="results"></div> 

/views/search/articles.js.cfm – This formats our returned dataset for return to the page

<!--- loop over and save the output in a variable--->
<cfsavecontent variable="resultSet">
<h2>Articles which match your search:  ( results)</h2>
<cfloop query="">
  <h3>#linkTo(text=title, controller="articles", action="view", key=id)#</h3>
  <h4>#Dateformat(createdAt, 'dd mmm yyyy')#</h4>

<!--- Use the plugin to shove back the results to the page --->
#pageReplaceHTML(selector="##results", content=resultSet)#

That’s it!
So the form at /views/search/index.cfm posts via ajax to the ‘q’ function in /controllers/Search.cfc.
That q() function returns the html via renderWith(), and the remote form helpers pageReplaceHTML() function posts the results to the results div on the calling page.

The nice part about this approach is that you can use that q() part with a switch/case statement to call any function to return data as you need. You could even go to a different search source, such as a Google Search Appliance, which I’ll cover soon.