How to use autocomplete to get first and last name from database

[BACFL]

I have tried the cfinput autocomplete and some jquery examples but I just can't get them working.

Does anyone have a full jquery or coldfusion example of how to autocomplete a text field using first and last name pulled from a database?

Thank you!

B.

[WolfShade]

I recommend not using CFFORM or any CFINPUT, CFSELECT, etc.  The binding can be appealing, but a few more keystrokes can get you better granular control, validation, etc., if done manually.  No veteran developer (that I am aware of) uses CFFORM et al for anything.  It started out as a great idea, but it uses an outdated javascript library that can sometimes glitch up on you.

A standard FORM can do at least as good as, more likely better than, CFFORM, granted it means a little more work on your part (it's totally worth it.)

Basically, using jQuery, you can set the onKeyDown or onKeyPress of the input fields to actuate a function that will use AJaX to send the field value to a CFFUNCTION in a .CFC component file that will query the database (SELECT firstName FROM users WHERE firstName like <cfqueryparam value="#form.firstName#%" /> -- notice the wildcard) and will return a query on each keystroke.  You use the query object converted to JSON to populate a variable and use a jQuery plugin to display the clickable options in an almost drop-down fashion.  jQueryUI has this capability.  Click here for an example.

HTH,

^_^

[Steve Sommers]

To expand on what WolfShade said, I recommend looking at jquery.typeahead: GitHub - twitter/typeahead.js: typeahead.js is a fast and fully-featured autocomplete library. It works great with AJAX calls or local data.

[BACFL]

Thanks for your answer.  In the meantime I have found this nice .cfc by Ben Forta.  However, I don't know how to make it work for last AND first name.  Any ideas?

<cfset THIS.dsn="cfartgallery">

    <!--- Lookup used for auto suggest --->

    <cffunction name="lookupArt" access="remote" returntype="array">

<cfargument name="search" type="any" required="false" default="">

<!--- Define variables --->

<cfset var data="">

<cfset var result=ArrayNew(1)>

<!--- Do search --->

<cfquery datasource="xxxxxxxxx" name="data">

SELECT lname, fname

FROM users

WHERE lname LIKE '#ARGUMENTS.search#%'

ORDER BY lname

</cfquery>

<!--- Build result array --->

<cfloop query="data">

<cfset ArrayAppend(result, lname)>

</cfloop>

        <!--- And return it --->

<cfreturn result>

    </cffunction>

</cfcomponent>

[BKBK]

autosuggest.cfm

<cfform name="empForm" id="empForm" action="#cgi.SCRIPT_NAME#">

<p>

Employee first name: <cfinput  autosuggest="cfc:employee.getFirstname({cfautosuggestvalue})" autosuggestminlength="1" type="text" name="firstName" size="50" typeahead="yes">

</p>

<p>

Employee ID: <cfselect name="employeeID" bind="cfc:employee.getEmpID({firstName})" />

</p>

<p>

<cfinput type="submit" name="sbmt" value="Send">

</p>

</cfform>

employee.cfc

<!--- Within same directory --->

<cfcomponent>

    <cfset selected_emp_id = "">

    <cffunction name="getFirstName" access="remote" output="false" returntype="array">

        <cfargument name="suggestedValue" required="true" type="string">

        <cfset var getEmpFirstName = queryNew("","")>

        <cfset var fnameArray = arrayNew(1)>

<!--- Uses datasource cfdocexamples which is in-built, hence already configured  --->

        <cfquery name = "getEmpFirstName" dataSource = "cfdocexamples">

            SELECT FirstName

            FROM Employees

        </cfquery>

        <cfloop query="getEmpFirstName">

        <cfset fnameArray[currentrow]=FirstName>

        </cfloop>

        <cfreturn fnameArray>

    </cffunction>

    <cffunction name="getEmpID" access="remote" returntype="array">

        <cfargument name="fName" required="true" type="string">

        <cfset var getEmployee = queryNew("","")>

        <cfset var empArray = arrayNew(2)>

        <cfquery name = "getEmployee" dataSource = "cfdocexamples">

            SELECT Emp_ID, FirstName || ' ' || LastName as empName

            FROM Employees

            WHERE FirstName = '#arguments.fName#'

        </cfquery>

        <!--- Values for the first - the default - select option --->

        <cfset empArray[1][1]="">

        <cfset empArray[1][2]="Select employee">

        <cfloop query="GetEmployee">

        <cfset empArray[currentrow+1][1]=Emp_ID>

        <cfset empArray[currentrow+1][2]=empName>

        </cfloop>

        <!--- Alternative, if you require no "Select employee" option --->

        <!--- <cfloop query="GetEmployee">

        <cfset empArray[currentrow][1]=Emp_ID>

        <cfset empArray[currentrow][2]=empName>

        </cfloop> --->

        <cfreturn empArray>

    </cffunction>

</cfcomponent>

[BACFL · Accepted solution]

I was ultimately able to do it with this .cfc

<!--- Lookup used for auto suggest --->

    <cffunction name="lookupname" access="remote" returntype="array">

<cfargument name="search" type="any" required="false" default="">

<!--- Define variables --->

<cfset var data="">

<cfset var result=ArrayNew(1)>

<!--- Do search --->

<cfquery datasource="xxxxxx" name="data">

SELECT lname + ',' + fname as fullname, userid

FROM users

WHERE lname LIKE '#ARGUMENTS.search#%'

ORDER BY lname,fname

</cfquery>

<!--- Build result array --->

<cfloop query="data">

<cfset ArrayAppend(result, fullname)>

<cfset ArrayAppend(result, userid)>

</cfloop>

        <!--- And return it --->

<cfreturn result>

    </cffunction>

</cfcomponent>

THE HTML:

<cfform>

<cfinput type="text"

        name="fullname"

        autosuggest="cfc:getname.lookupname({cfautosuggestvalue})">

</cfform>

[BKBK]

I am sorry to say, your answer is incorrect.

For the autosuggest part, use the ideas in my getFirstName(), in particular

<!--- Build one-dimensional result array --->

<cfloop query="data">

<cfset ArrayAppend(result, fullname)>

<cfset ArrayAppend(result, userid)>

</cfloop>

In the second step, after the user is selected, you need to be able to identify her by ID, hence a 2-D array:

<cfloop query="data">

<cfset ArrayAppend(result[1], userid)>

<cfset ArrayAppend(result[2], fullname)>

</cfloop>