Populate HTML Table Data from CFC Query Results

I have an Ajax request that calls a CFC:

Ajax:

<script>
function PopulateEmailData(){

        $.ajax({
            type: "POST",
            data: $('##emailTicket').serialize(),
            url: "cfcs/emailData.cfc?method=getData&ticket_id=#url.ticketID#",
            beforeSend: function(){
                //$('.loader').show();
            },
            complete: function(){
                 //$('.loader').hide(3000);
            },
            success: function(data) {
                $("##timestampTD").append('emailTracking.timestamp'); 
                $("##senderTD").val('emailTracking.sender');
                $("##recipientTD").val('emailTracking.recipient');
                console.log("PopulateEmailData Called - Success");
            },
            error: function() {
                console.log("PopulateEmailData Called - Error");
            }
        })
    }
    console.log("PopulateEmailData Called");    
</script>

My CFC then queries my database: CFC

<cfcomponent>
    <cffunction name="getData" access="remote" returnType="query">
        <cfargument name="ticket_id" type="any" required="true">

        <!--- localize function variables --->
        <cfset var emailTracking = "">

        <cfquery name="emailTracking" datasource="#datasource#">
            SELECT *
            FROM  email_tracking
            WHERE ticket_id = <cfqueryparam value="#ARGUMENTS.ticket_id#" cfsqltype="cf_sql_varchar">
        </cfquery>

        <cfreturn emailTracking>
    </cffunction>
</cfcomponent>

I can see in my console output that my query data is being returned. What I am trying to do is to fill HTML table data with the results. I am attempting to do it through the success function of my AJAX call.

Here is my HTML table

<table width="100%" class="email_tracking_table">
    <thead>
        <tr>
            <th>Timestamp</th>
            <th>Sender</th>
            <th>Recipient(s)</th>
        </tr>
    </thead>
    <tr>
        <td id="timestampTD"></td>
        <td id="senderTD"></td>
        <td id="recipientTD"></td>
    </tr>
</table>

But what I am seeing is just the text 'emailTracking.timestamp' being added to the TD element, not the retuned data. What am I doing wrong?

Solution

Problems

If your query can return multiple records, your JS code will need to loop through those results, adding a new <tr> for each record.
Wrapping a JS variable in quotes prevents it from being evaluated. So 'emailTracking.timestamp' (note the quotes) is a string, not a variable.
The actual variable passed to the success() function is named data, not "emailTracking.timestamp". To access the query data, use the data variable.
The CFC may be returning a query, but it's not in the correct format. CF returns wddx format by default, which your jQuery code won't handle correctly. Use a more compatible format like JSON.

Resolution

To request the response in JSON format, add the returnFormat parameter to the ajax url:
```
?method=getData&returnFormat=json&....
```

Set the jquery response dataType to "json", so its automatically deserialized

    ...
    type: "POST",
    dataType: "json",
    url: "..."
    ...

Although adding ?returnFormat=json to the url is enough to get back JSON, CF's default format for queries is very ... non-standard. So you may want to modify the CFC to return an array of structures instead. Just don't forget to change the function return type to "array".

<cffunction name="getData" access="remote" returnType="array">     
    ... code

    <cfset local.response = []>
    <cfloop query="emailTracking">
        <cfset arrayAppend(local.response
                          , { "timestamp": emailTracking.timestamp
                              , "sender": emailTracking.sender
                              , "recipient": emailTracking.recipient
                            }
                        )>
    </cfloop>

    <cfreturn local.response>

</cffunction>

With those changes, the JS data variable will now contain an array of structures which you can loop through and construct your HTML easily enough.

success: function(data) {
    data.forEach(function (item, index) {
        // for demo, log values in each row to console
        console.log(index, item.timestamp, item.sender);
    });
}