Populate HTML Table Data from CFC Query Results

Question

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?

There are 2 ways to handle this. Have the CFC method return JSON and then use jQuery to populate the table or have the CFC method loop over the query create the HTML and then return it for the ajax just to include it. The former method is the best approach but the latter is easier if you dont know jQuery. — Cory Fail, Jul 22 '19 at 15:48
You are currently appending a constant string to your table cell. Look at how your data is returned. In the `success` handler write: `console.dir(data)`. Then you can see how to access the value you want to write into the cell. — Bernhard Döbler, Jul 22 '19 at 15:52
I'm not following. So I should be referencing my query results in a different way instead of $("##timestampTD").append('emailTracking.timestamp'); ? Can you give an example? My console.dir(data) doesn't show my data in there. — Brian Fleishman, Jul 22 '19 at 16:56
The .append() method takes an html string. Output the data that’s passed into your success function and see how your data is formatted in JavaScript. Then pass that data into the append() function. — Redtopia, Jul 22 '19 at 18:19

SOS · Accepted Answer · 2019-07-23T06:59:51.083

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);
    });
}

Awesome explanation! Really helps in my understanding of how this whole process works. Thanks so much. — Brian Fleishman, Jul 23 '19 at 11:56
Because CF has always been a little wonky with returning data, especially where JSON comes into play, I've always found it more reliable to do like Ageax suggests and build a specific return value. — Shawn, Jul 23 '19 at 18:41
@Shawn - I didn't think S.O. would appreciate my real thoughts on the default format, so I was being ... "tactful" ;-) — SOS, Jul 23 '19 at 19:40
@BrianFleishman - I don't know what version of CF you're running, but [here's another (shorter) method](https://stackoverflow.com/a/57212161/8895292) for CF 2016+. — SOS, Jul 27 '19 at 19:41

Populate HTML Table Data from CFC Query Results

1 Answers1