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

Participant ,
Oct 27, 2016 Oct 27, 2016

Copy link to clipboard

Copied

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.

Views

1.9K

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

Participant , Oct 27, 2016 Oct 27, 2016
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, useridFROM usersWHERE lname LIKE '#ARGUMENTS.search#%'ORDER BY lname,fname</cfquery><!--- Build result ar...

Likes

Translate

Translate
LEGEND ,
Oct 27, 2016 Oct 27, 2016

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Oct 27, 2016 Oct 27, 2016

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 27, 2016 Oct 27, 2016

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Oct 27, 2016 Oct 27, 2016

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Oct 27, 2016 Oct 27, 2016

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Oct 27, 2016 Oct 27, 2016

Copy link to clipboard

Copied

LATEST

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines