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.
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 ar
...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,
^_^
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.
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>
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>
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>
Copy link to clipboard
Copied
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>