Whatterz


Using jQuery Auto-Complete with ColdFusion

by Simon. Average Reading Time: about 2 minutes.

Creating an autocomplete form field historically has not been a trivial matter and would require an indepth knowledge of JavaScript and CSS. However, the task is made far more simple when using one of the many freely-available JavaScript libraries. In this post I will show you how to implement the jQuery Autocomplete created by Dylan Verheul.

The Goal

Allow the user to type a few characters into a standard form text input field and to automatically provide suggestions from which the user can select.

Prerequisites

  1. The latest copy of jQuery
  2. A basic understanding of JavaScript
  3. A server-side script that can respond to the AJAX request, in our case ColdFusion

Demo

The demo will specifically look at a simple form text input field, which takes a country name.

See the demo, and others, in action

How It Works

Once the user begins to type into the form text input field, the jQuery autocomplete is activated. After a set time interval, a list of items is displayed below the input field. The user can select these with either the arrow keys or mouse.

The Code

There are three parts to this demo:

  1. The page’s HTML.
  2. The server-side code to produce the dynamic page (i.e. to load the autocomplete div when the user types something into the input field).
  3. The jQuery & JavaScript.

HTML Form

<h3>Example 1.: Country Lookup</h3>
<p>Using <abbr title="Asynchronous JavaScript and XML">AJAX</abbr> to interrogate the database.</p>
<p>Example data: Australia, Bulgaria, United Kingdom</p>
<form name="frmAutoCompleteCountry" id="frmAutoCompleteCountry" action="#" method="post">
<p>
<label for="country">Country</label>
<input type="text" name="country" id="country" />
</p>
</form>
<p>NB. If you have <a href="http://getfirebug.com/" title="Get Firebug">Firebug</a> installed you will be able to view the <abbr title="Asynchronous JavaScript and XML">AJAX</abbr> call.</p>

ColdFusion

This is a simple example, using a database to return a list of country names that match the characters the user has input. You could expand this and return a JSON data structure.

<cfsetting enablecfoutputonly="true">
<cfquery name="qryGetCountry" datasource="myDatasource">
SELECT countryName
FROM Country
WHERE countryName LIKE <cfqueryparam value="#URL.q#%" cfsqltype="cf_sql_varchar" />
</cfquery>
<cfoutput query="qryGetCountry">
#qryGetCountry.countryName##chr(10)#
</cfoutput>

JavaScript

The JavaScript will attach itself after the document is ready, i.e. after the page has loaded. Each time the text input field, with the ID of country, is changed, the autocomplete event is fired. This makes a call to the ColdFusion page, which returns a list of matched items.

<script type="text/javascript" src="jquery-1.2.6.min.js"></script>
<script type="text/javascript" src="jquery.autocomplete.js"></script>
<link type="text/css" href="autocomplete.css" rel="stylesheet" media="screen" />
<script type="text/javascript">
$(document).ready(function() {
	$("#country").autocomplete(
		"country.cfm",
		{
			minChars:2,
			delay:200,
			autoFill:false,
			matchSubset:false,
			matchContains:1,
			cacheLength:10,
			selectOnly:1
		}
	);
);
</script>

Where To Take It Next

JSON

The above example only shows a simple text list, separated by carriage returns. It is more preferable to use JSON.

Unobtrusive JavaScript

As with any page that is loaded with JavaScript and AJAX functionality, it should work without JavaScript.

To achieve this with the above tutorial, you will need to replace the jQuery autocomplete functionality with an ‘interim’ page that allows a user to select from a list of items, effectively turning the input field into a simple search interface. Of course, all other form field information would need to be retained between pages.

Download The Code

The example code can be downloaded from the demo page. Included are ColdFusion and PHP examples.

This article has been tagged

, , , , , , , , , , , ,

Other articles I recommend

Using the MooTools Autocompleter Plugin with ColdFusion

In a previous post, I demonstrated how to implement Dylan Verheul’s jQuery Autocomplete plugin. Not content with demonstrating one library’s plugin, it is now the turn of Mootools.

jQuery 1.2 API Reference

Among the plethora of JavaScript libraries to have been released, few have been recognised to be as effective as jQuery. This lightweight library has been the subject of different discussions since it was launched in 2006. Basically, jQuery has the ability to flawlessly string together JavaScript together with HTML. Because of its effectiveness, there have different types of lightweight applications and plug-ins launched using jQuery. Ajax based websites that offers simple interface would virtually work together using jQuery’s simple interface.

Parsing Twitter Usernames, Hashtags and URLs with JavaScript

As part of an AIR project that I have been working on with my good friend Rob, we came across the need to parse a number of URLs within the text of a Twitter post. This may not sound too easy at first, but thanks to the prototype property available on JavaScript objects, our task was a relatively simple one.

  • http://fernandotrevisan.com.br/tecnologia/ Fernando S. Trevisan

    “Fantástico”, as we say in Portuguese 😉

  • http://bassistance.de Jörn Zaefferer

    The successor to Dylan’s autocomplete plugin can be found here: http://plugins.jquery.com/project/autocompletex

    There are a few API changes and a ton of improvements and new features (eg. multiple complete ala GMail to-field).

    May be worth a try!

  • Bernie

    Simon, thanks for posting this code. I see the potential of it, but I keep getting “Unknown runtime error” messages in JavaScript over and over. I’m trying to call a ColdFusion page and even just tried to repeat your example, but still get issues.

  • http://www.simonwhatley.co.uk Simon

    @bernie it’s a little difficult to debug, but you’ll need to make sure that

    1. The data is returned in the correct format
    2. The cfm file that the auto-complete javascript calls is accessible
    3. That you’re using the url.q variable in your query

  • Bernie

    Yeah it is difficult to debug, although Firebug makes it a bit easier. It’s finding the CFM page and I have been able to verify that it’s passing in url.q correctly. For some reason, an Access version of my query works as well, but the Oracle query doesn’t. Also, it only seems to work in Firefox for me, versus IE and Firefox for you. I’ll give it a couple more tries and give it up today if I can’t get it worked out.

    Thanks!

  • Bernie

    Duh Simon, I had forgotten to add LOWER() to my Oracle query…*slap self*…now it works in 100% in Firefox, but not in IE. Since IE is the version my customers use, I have to figure out why I keep getting the runtime errors there…

  • Bob

    Simon – I’m using Coldfusion and can’t get this to work. I don’t get any errors but it doesn’t auto complete either. I am missing something but not sure what.

    I put this in the index.cfm page and have country.cfm and all the js and css file(s) in the same directory:

    Auto Complete – jQuery

    $(document).ready(function() {
    $(“#country”).autocomplete(
    “country.cfm”,
    {
    minChars:2,
    delay:200,
    autoFill:false,
    matchSubset:false,
    matchContains:1,
    cacheLength:10,
    selectOnly:1
    }
    );
    );

    Example 1.: Country Lookup
    Using to interrogate the database.
    Example data: Australia, Bulgaria, United Kingdom

    Country

  • Bob

    opps. i should know better. my question is what should the index.cfm file contain? I think my country.cfm is ok. Thanks!

  • http://www.simonwhatley.co.uk Simon

    @Bob The complete code is provided for download. Everything you need is contained within.

  • Bob

    Thanks Simon – I did get it to work.
    Follow up question –

    If the user types in: ‘street main’ in the search box,
    I would like ‘123 main street’ to be returned as a result.

    Are there settings to allow that?

    Thanks!

  • http://www.simonwhatley.co.uk Simon

    @Bob.

    Your request is a little complicated. If you want to find every instance that is like ‘main street’, you will need to amend your database query to include the wildcard operator %.

    e.g.

    SELECT column1, column2
    FROM tableName
    WHERE column1 LIKE ‘%#URL.q#%’

    Obviously you need to substitute the query for your own and make sure you use cfqueryparam around the variable.

    If you want to return anything that contains the words ‘main’ and or ‘street’, you will have to enable full text searching on your database column (if using MSSQL). Using full text searching is a whole different topic that I’ll not go into here.

  • Bob

    Thanks Simon…
    I was thinking something like this to “return anything that contains the words ‘main’ and ’street’”.
    Treat url.q as a list, separated by spaces, and loop through the list

    Would that work?

    select column1, column2
    from tableName
    where 1=1

    and upper(column1) LIKE

  • Philippe Y.

    Hi,

    After inserting the js, css and cfm code I’m still having some trouble.

    I’ve got an error « Object doesn’t support this proprety or method » at this line :

    $(document).ready(function() {

    Do you have any idea ?

  • http://www.simonwhatley.co.uk Simon

    @philippe you need to make sure all the files are installed in the correct order in the head of your html document. The jQuery JavaScript must be first.

  • http://mattcrouch.com MattC

    Thank you very much for the code, Simon. Works great! One question I do have is that it seems to only query once I type in 2 letters, do you know how I can change it to start querying with 1 letter inputted?

  • http://mattcrouch.com MattC

    Hey never mind, i totally overlooked the part for that :)

  • Jaime

    Simon:

    Thanks for posting this code. After playing around with other methods I did get you code to work great.

    However, for me it only works in Firefox and not in I.E.. Am I the only one with this issue. I am using I.E. 8.

    I hoping there is a workaround.

  • http://johnshultis.org John

    I think you’re javascript example there is missing a }. The very last line should be “});” not “);”.

    That may be why some people are having trouble with this…

  • Dana

    I have tried this and it works. I was wondering was there away to bring back the ID, as a hidden field?

    SELECT id, column1+ ‘ ‘ + column2 AS full_name FROM Demographics
    WHERE column2 LIKE ‘#URL.q#%’

    Is there a way that i can also bring back the id, but as a hidden field.

  • Paul

    Very nice code. however, for me too it won’t work in IE 7. The drop down list appears for about a quarter of a second and then disappears

  • Paul

    What appears o be happening is when the drop down list is displayed, the textbox loses focus

  • Steve Caldwell

    Is it just me or does CF9 kill jQuery Autocomplete? I upgraded servers and poof. I know the jQuery is working because the box indents as if to display the results but then nothing happens, as if the query data isn’t being passed through properly. Anyone else experiencing this?

  • Steve Caldwell

    above solved … ok for some reason my query on the coldfusion side had both “dbtype=”query” and datasource=”ds” tags and it worked in CF8…I removed the dbtype=”query” tag and it works fine. I don’t even know why that was there in the first place. (probably from a different example on the web somewhere)

  • http://GB-dev.seagulllighting.com datacloud

    Great stuff! implemented very easily. How can I make the form auto submit on the selection of the autofill selection by using the enter key.

    If I click on the result with my mouse for submits. if I use the down arrow and enter it fills the select box but does not submit.

    GB-dev.seagulllighting.com enter 32 for autoselect list

  • Zach

    I LOVE this code so much …

    But really – I am wondering the same as Paul. Would like to know if the ID can be brought back and somehow attached to the value of the form without actually displaying it? Hmmmm.

    Thank you very much

  • Pingback: 21 Jquery Autocomplete Tutorials | Developerholic()

  • Pingback: 21 Jquery Autocomplete Tutorials | free wp themes()

  • Deepesh Kapadia

    Hey i cant seem to make this work in coldfusion. Can you please help me out on this?

  • http://www.simonwhatley.co.uk/ Simon Whatley

    @deepesh what isn't working for you? Remember, most of the functionality is client-side. So long as your ColdFusion code returns valid JSON, it should be okay.

  • Lukenukum

    very handy, many thanks for the code Simon.

  • Jezz

    Hi Simon! Thanks for posting this. I tried to run your code and it’s great. But when I changed it a bit, I couldn’t get it working properly. What I did was, I edited country.cfm and implemented my own query. I used QueryToArrayOfStructures and jsonencode from CFLib.org to produce a json data. In the index, I got stuck in parsing the json string to get the value I needed. When I type in the text field, I get “undefined” as the search result. But when I click that, the whole json data (with the correct values) appears in the field. My json format looks like this: [{“name”:”aaa”}, {“name”:”bbb”}]. Can you help me with the parsing? Thanks!

  • Monique

    This doesn’t include a coldfusion example.