6 Replies Latest reply on Oct 27, 2016 10:09 PM by BKBK

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

    BACFL Level 1

      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.

        • 1. Re: How to use autocomplete to get first and last name from database
          WolfShade Level 4

          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,

           

          ^_^

          • 2. Re: How to use autocomplete to get first and last name from database
            Steve Sommers Level 4

            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.

            • 3. Re: How to use autocomplete to get first and last name from database
              BACFL Level 1

              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>

              • 4. Re: How to use autocomplete to get first and last name from database
                BKBK Adobe Community Professional & MVP

                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>

                • 5. Re: How to use autocomplete to get first and last name from database
                  BACFL Level 1

                  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>

                  • 6. Re: How to use autocomplete to get first and last name from database
                    BKBK Adobe Community Professional & MVP

                    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>