jQuery UI Autocomplete Widget with ColdFusion

You may also be interested in the jQuery UI Autocomplete Widget with PHP and MySQL or the jQuery UI Autocomplete Widget with ASP.NET VB post. Also, check out the Using jQuery Autocomplete to Populate Another Autocomplete post.

The jQuery UI folks have released an autocomplete widget that is pretty slick. Using it with ColdFusion is a snap. This example uses the serializeJSON function in ColdFusion 8. If you have an earlier version of CF, you will have to find a cfc that can build a JSON string for you. There are several freely available cfc’s that do it for you.
autocomplete
This example will use US states and territories to populate the autocomplete. It will also demostrate how to fill other fields with data returned from the database. This data can be used to fill a visible text box or a hidden form field. It also demonstrates the basic autocomplete functionality which may be fine for some applications.

Of course, you will need the jQuery core file, the jQuery UI core file, and the jQuery UI style sheet of choice. The style sheet comes from the themes available in the jQuery UI website and can be downloaded with the core file or you can link to the latest versions of both the core files and the css:

<link rel="stylesheet" type="text/css" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1/themes/redmond/jquery-ui.css">
 
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1/jquery-ui.min.js"></script>

The HTML is straight forward and stripped down for the example:

<form action="index.cfm"  method="post">
<fieldset>
<legend>jQuery UI Autocomplete Example - ColdFusion Backend</legend>
<p>Start typing the name of a state or territory of the United States</p>
<p class="ui-widget"><label for="state">State (abbreviation in separate field): </label>
	<input type="text" id="state"  name="state" /> <input readonly="readonly" type="text" id="abbrev" name="abbrev" maxlength="2" size="2"/></p>
    <input type="hidden" id="state_id" name="state_id" />
<p class="ui-widget"><label for="state_abbrev">State (replaced with abbreviation): </label>
<input type="text" id="state_abbrev" name="state_abbrev" /></p>
<p><input type="submit" name="submit" value="Submit" /></p>
</fieldset>
</form>

As a bonus, we dump out the form values to see what we have right underneath the form itself:

<cfdump var="#form#" label="Form Fields" />

And the jQuery on the page is equally brief:

$(function() {
        
            $('#abbrev').val("");
            
            $("#state").autocomplete({
                source: "states.cfm",
                minLength: 2,
                select: function(event, ui) {
                    $('#state_id').val(ui.item.id);
                    $('#abbrev').val(ui.item.abbrev);
                }
            });
            
            $("#state_abbrev").autocomplete({
                source: "states_abbrev.cfm",
                minLength: 2
            });
        });

Notice that there are two autocomplete functions on the page, one for each example in the demo. Each function calls a different ColdFusion file which return slightly different result sets.

Also, the minLength for autocomplete to return results is set to 2 to prevent too many rows from being returned.

The jquery autocomplete will append the text typed into the autocomplete field as the URL parameter ‘term.’ This URL parameter is used to query the database.

From the jquery documentation:

The request parameter “term” gets added to that URL.

Both ColdFusion pages return the data after a few steps:

  1. It queries the database
  2. Loops the query, adding each row to a structure that is appended to an array
  3. Outputs the array as JSON data

The states.cfm file returns the id field, the state field as ‘label’, and the abbrev field. These values are placed in the appropriate text boxes by the autocomplete jQuery function.

<cfset returnArray = ArrayNew(1) />

<cfquery name="qryStates" dataSource="autocomplete">
    Select * from states where state like <cfqueryparam value="%#URL.term#%" cfsqltype="cf_sql_varchar">
</cfquery>

<cfloop query="qryStates">
    <cfset statesStruct = StructNew() />
    <cfset statesStruct["id"] = id />
    <cfset statesStruct["label"] = state />
    <cfset statesStruct["abbrev"] = abbrev />
    
    <cfset ArrayAppend(returnArray,statesStruct) />
</cfloop>

<cfoutput>
#serializeJSON(returnArray)#
</cfoutput>

Unfortunately, the ColdFusion function serializeJSON does not put query results in the format that javascript likes. That’s why we have to do the hokey-pokey with the structure and array.

Very important information below. Please read and understand before expecting the autocomplete to work properly.

The states_abbrev.cfm shows the basic functionality of the autocomplete function by just assigning results of the query to the ‘label’ and ‘value’ fields. Explanation on the ‘label’ and ‘value’ fields from the jQuery UI site:

The local data can be a simple Array of Strings, or it contains Objects for each item in the array, with either a label or value property or both. The label property is displayed in the suggestion menu. The value will be inserted into the input element after the user selected something from the menu. If just one property is specified, it will be used for both, eg. if you provide only value-properties, the value will also be used as the label.”

<cfset returnArray = ArrayNew(1) />

<cfquery name="qryStates" dataSource="autocomplete">
    Select * from states where state like <cfqueryparam value="%#URL.term#%" cfsqltype="cf_sql_varchar">
</cfquery>

<cfloop query="qryStates">
    <cfset statesStruct = StructNew() />
    <cfset statesStruct["label"] = state />
    <cfset statesStruct["value"] = abbrev />
    
    <cfset ArrayAppend(returnArray,statesStruct) />
</cfloop>

<cfoutput>
#serializeJSON(returnArray)#
</cfoutput>

Usual recommended jQuery and CF reading:

Demo

Posted in ColdFusion, jquery. Tags: , . Permalink. Both comments and trackbacks are closed.

43 Comments

  1. joel
    January 10, 2012 at 5:10 pm | Permalink

    Jeff – it could be something as simple as not having in agency.cfm. Post the full code.

  2. January 10, 2012 at 5:07 pm | Permalink

    @Jeff

    Couple of quick things to check. Make sure you are filtering by term unless you want all the results return. Also, you need a ‘label’ and/or ‘value’ field returned in the json. See the last code snippet in the post. You can return and use other data but the autocomplete needs one or both of those fields. Explanation in post.

  3. Jeff
    January 10, 2012 at 5:01 pm | Permalink

    Hi there,

    Great tutorial! very informative. However, I can’t get mine to work. I followed your guidelines verbatim, using different data. I’m not sure if it’s just not getting to the file that runs the query, but basically, when I start typing, nothing happens. Any help would be greatly appreciated!! Here’s the code for the file with the form:

    TEST

    /*$(document).ready(function() {*/
    $(function() {

    $(“#agency”).autocomplete({
    source: “agency.cfm”,
    minLength: 2,
    select: function(event, ui) {
    $(‘#agency_id’).val(ui.item.id);
    //$(‘#abbrev’).val(ui.item.abbrev);
    }
    });
    });

    jQuery UI Autocomplete Example – ColdFusion Backend
    Start typing the name of an agency
    Agency

    And here’s agency.cfm:

    SELECT * FROM Agencies WHERE Agency LIKE ‘%%’

    #serializeJSON(returnArray)#