Skip to main content
B
BACFL
Inspiring
October 27, 2016
Answered

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

  • October 27, 2016
  • 1 reply
  • 3096 views

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.

This topic has been closed for replies.
Correct answer BACFL

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>

1 reply

WolfShade
WolfShade
Legend
October 27, 2016

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,

^_^

B
BACFLAuthor
Inspiring
October 27, 2016

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>

B
BACFLAuthorCorrect answer
Inspiring
October 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, 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>

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices