Sunday 26 February 2012

JQuery Autocomplete Using ColdFusion CFCs

I recently had to create a form text field that used jQuery autocomplete on a ColdFusion website and I thought it was pretty cool the way it worked so I thought I would share. Here it is in action...


This form can be broken into 3 fairly simple parts: The basic HTML that displays the form, the CFC that queries the database and the jQuery that ties it all together. Let's start off by looking at the form code...
Nothing to see here really, just an input text field and a submit button that doesn't do anything yet. Next up is the CFC we use to query the database...

The queryNames function is fairly similar to any other function you might write to query a database except it limits the amount of rows returned to 10. If you don't do this your autocomplete list will come out too long and could possibly take a while to load if you have a lot of rows in your table.

And finally we bring the two together with jQuery...

Here we use Ajax to call the queryNames method in our cfc, passing in the current value of the text field, then jQuery uses the response to populate the autocomplete widget. Pretty cool, huh?

Hope this helped!
Posted by at

19 comments:

  1. Alex Koumoundouros13 August 2012 at 17:35

    Thank you this was extremely helpful.

    ReplyDelete
  2. Larry C. Lyons22 July 2013 at 07:54

    I've had a lot of luck with the jQueryUI autocompete. Generally its been a bit easier than putting it together yourself, plus it makes the styling of the dropdown and input text box easy. Here's the code I'd use using your example:

    $(function() {
    $("#input").autocomplete({
    source: "/autocomplete.cfc?method=queryNames&returnformat=json",
    minLength: 3,
    select: function(event, ui) {
    $('#input').val(ui.id);
    }
    });
    });

    hth,
    larry

    ReplyDelete
    Replies
    1. Jack Traynor22 July 2013 at 20:07

      Thanks Larry, I'm glad you found this useful.

      Delete
    2. Monique Boea14 November 2013 at 08:09

      Larry can you post all of your code?

      Delete
  3. Unknown22 July 2013 at 10:06

    Can't make it work... could you supply setup script. Don't worry about data.

    wonder if "searchPhrase: query.term" is specific to your table?

    Thanks

    ReplyDelete
    Replies
    1. Unknown22 July 2013 at 10:22

      what version of Jquery and do you have to have coldfusion ajax enabled

      Delete
    2. Jack Traynor22 July 2013 at 20:03

      Jon, I use jQuery 1.7.1 and jQuery UI 1.8.16, but it should work the same for later versions too.

      "searchPhrase" is the argument that my ajax function accepts and "query" is something that jQuery autocomplete passes to the response function, response.term contains what is currently in the text box.

      Here I have manually created a query object so I don't need a database for this small example. I then do a Query of Queries on that object to make it look like a database call. Here is the code that I use to create the "names_table" query...

      <!--- Each item in this list will be a row in the query --->
      <cfset tempStr = "Rexx,Forth,OCaml,Smalltalk,Scala,D,Erlang,Cobol,ColdFusion,Lua,Ada,Fortran,Tcl,Haskell,Scheme,Pascal,Delphi,Lisp,Objective C,Actionscript,Assembly,Visual Basic,Shell,Ruby,SQL,Perl,C##,Python,JavaScript,PHP,C++,Java" />
      <!--- Initializes the new Query --->
      <cfset names_table = QueryNew( "" ) />
      <!--- Converts the list to an array so it can be passed to the QeuryAddColumn function --->
      <cfset tempStr = ListToArray(tempStr, ",") />
      <!--- Inserts each item in the list into the Name column of the query object --->
      <cfset QueryAddColumn(names_table, "Name", "VarChar", tempStr) />

      <cfdump var="#q#" />



      If you put the above code into the queryNames function, above the query_names query, the data side of this example should work.

      I hope this helps you get it working, good luck!

      Delete
  4. Unknown22 July 2013 at 21:45

    Thank you very much...

    http://boise-airporttaxi.com/aj3.cfm


    Cool stuff

    ReplyDelete
    Replies
    1. Unknown23 July 2013 at 05:14

      It would not work with jquery "1.10.2/jquery.min.js"

      It needed both of these
      /jquery/1.7.1/jquery.min.js
      /jqueryui/1.8.16/jquery-ui.min.js
      to work properly

      Don't get it but....

      Delete
    2. Monique Boea21 October 2013 at 21:20

      I can't get this to work

      Delete
  5. unclesol12 August 2013 at 09:43

    For some reason, when I attempt this, my response is returning ahead of the JSON results. Any idea why this could be?

    ReplyDelete
    Replies
    1. unclesol12 August 2013 at 09:45

      This filtered out the tag in my post - it should say "returning DOCTYPE html ahead of the JSON"

      Delete
    2. Jack Traynor12 August 2013 at 16:38

      That's strange, does the url option in your ajax call have "&returnformat=json" in it? Having that option on the url should convert whatever your function returns into JSON.

      If that isn't it I will need some more information to help out, are you using CF? Can I see the function you're calling by ajax?

      Delete
  6. Ed T30 September 2013 at 18:15

    Would it be possible to adapt this to work on MX6 or MX7?

    If so, would you be kind enough to explain how?

    Thanks.

    ReplyDelete
    Replies
    1. Jack Traynor30 September 2013 at 18:44

      Most of the work here is done in the JavaScript so the ColdFusion side is fairly basic and should work in CF7 as far as I can tell. Have you tried it? What functions aren't working?

      CF6 I'm not too sure. Does CF6 support CFCs? If not you might have to write a .cfm file that queries the database, formats the result as JSON and prints it to the screen, then point the AJAX url to that page. Hard work.

      Hope that makes sense.

      Delete
    2. Ed T1 October 2013 at 01:19

      This comment has been removed by the author.

      Delete
  7. Ed T1 October 2013 at 01:21

    Thanks for your prompt response Jack. Unfortunately, I do not have the luxury of using JSON in MX6.1. So, I've ended up using a very crude hack as per below.

    Retrieve list of names from database
    cfquery name="Staff" datasource="Intranet">
    select Name
    from StaffList
    order by Name
    /cfquery>


    Convert single quotes to double quotes. Two step approach to avoid incorrectly replacing valid apostrophes in certain surnames.
    cfset NameList = ReReplace(QuotedValueList(Staff.Name),"','",'","','All')>
    cfset NameList = '"' & Mid(AdvList,2,Len(NameList)-2) & '"'>

    Output our converted string to the script
    cfoutput>
    script>
    $(function() {
    var validNames = [#NameList#];
    $( "##input" ).autocomplete({
    source: validNames
    });
    });
    /script>
    /cfoutput>

    ReplyDelete
    Replies
    1. Ed T1 October 2013 at 01:22

      I had to remove the < brackets above as the code was not displaying properly.

      Delete
  8. Admin26 July 2017 at 23:29

    take a look at this autocomplete, it's awesome, http://justwebcode.blogspot.com/2017/07/autocomplete-textbox-with-jquery.html

    ReplyDelete

Subscribe to: Post Comments (Atom)