jquery - Populate Select list with AJAX

Question

So I am a real JQUERY novice, so please go easy on me.

I would like to populate a CFSELECT using data returned from a CFC. Here is my working CFC:

<cfcomponent>
<cffunction name="getDescriptions" access="remote" returnformat="json" returntype="string" output="false">
    <cfquery name="customDescriptions" datasource="#datasource#">
        select description
        from service_descriptions
        where description <>"ADD NEW"
        order by description
     </cfquery>

    <cfset data = serializeJSON(customDescriptions)>
    <cfreturn data>
</cffunction>

Here is how the data from my CFC is returned:

---->Choose from the list<----Backup MaintenanceMalware RemovalMonthly Server MaintenanceNetwatch Alert - High CPU usageNetwatch Alert - Low Disk space on CNetwatch Backup AlertNew Employee TrainingPerform monthly tune-ups on workstationsTesttest2test3test4test5Weekly MaintenanceWhite-list Request

I am strugling with my AJAX code to populate my CFSELECT form element.

<cfselect name="descriptionDD4" id="descriptionDD4">
   <option value="add_new">ADD NEW</option>
</cfselect>

Here is what I have so far with my AJAX but it doesn't work.

 <script>
       $(document).ready(function CheckAjaxCall()
            {
                $.ajax({
                    type:'POST',
                    url:'cfcs/get_descriptions.cfc?method=getDescriptions',                    
                    dataType:'json',
                    cache:false,
                    success:function(customDescriptions){ 

                $('##descriptionDD4').get(0).options.length = 0;
                $('##descriptionDD4').get(0).options[0] = new Option("--Select--", "0");        

                $.each(description, function(i,item) {
                                    $('##descriptionDD4').get(0).options[$('##descriptionDD4').get(0).options.length] = new Option(item[i].Name, item[i].roll);
                                                                                                                    // Display      Value
                            });

                            },
                            //error:function(){alert("Connection Is Not Available");}
                        });

                        return false;
                    });
            </script>

Any help would be greatly appreciated. Thanks. -Brian

Answer 1

Note: The way CF serializes queries by default is wonky. In the long run you may want to roll-your-own and return something more typical (and intuitive), like an array of structures. But it is still worthwhile to understand why your current code is not working, IMO.

Issue:

As Scott pointed out, the biggest problem is that your javascript code is expecting one format, but your cfc is returning a different format. You need to change one of them, so they are both in synch. Also, as I mentioned in the comments, using cfselect does not buy you anything here, so just use a plain html select instead.

Debugging:

Before you can do anything with the response from the CFC, you need to understand the format of what it is sending back. Start simply. Just call the cffunction when the page loads and log the response to the console. You can wrap everything up in a function later, after it is working.

<script type="text/javascript">
$(document).ready(function(){
    // Important: Must append the parameter "&returnformat=json"
    $.ajax({
       url: 'cfcs/get_descriptions.cfc?method=getDescriptions&returnformat=json'
       , dataType: 'json'
       , success: function(response){
           console.dir(response);
       },
       error: function(msg){
           console.log(msg);
       }
    })
}); 
</script>

Using FF's web console, you will see the result is a structure containing two keys: COLUMNS and DATA.

DATA is a multi-dimensional array, containing your query results. It is indexed by row and column number. You can loop through it, same as in CF. The only difference is the index will be zero based (and of course key names are case sensitive in JS). Add the code below to your success function and you will see the query values displayed in the Web Console.

 // display values for debugging purposes
 for (var i = 0; i < response.DATA.length; i++){
    console.log("value in row ["+ i +"] column [0] = "+ response.DATA[i][0]);
 }

Usage:

Once you understand how to access the data, it is just a matter of using it to populate the list. You can either use the for loop to append options individually, or plug the DATA array into the $.each function, using the method described here. Since your query only returns a single column, I used it for both the option text and value.

$.each(response.DATA, function(i, row){
            // get value in first column ie "description"
            var description = row[0];

            // append new option to list
            $("#descriptionDD4").append($('<option/>', { 
                    value: description,
                    text : description 
            }));
        });

All that is left is to wrap the ajax call in a function you can call wherever needed. But you should be able to figure that part out on your own.

Hopefully this shed a little light on working with remote cffunctions from jQuery.