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
– Cory Fail, Commented Jul 22, 2019 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
– Bernhard Döbler, Commented Jul 22, 2019 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
– Brian Fleishman, Commented Jul 22, 2019 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
– Redtopia, Commented Jul 22, 2019 at 18:19

SOS · Accepted Answer · 2019-07-23 06:59:51Z

4

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

edited Jul 23, 2019 at 6:59

answered Jul 23, 2019 at 6:47

SOS

6,5602 gold badges14 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Brian Fleishman Over a year ago

Awesome explanation! Really helps in my understanding of how this whole process works. Thanks so much.

SOS Over a year ago

Glad it was helpful.

Shawn Over a year ago

<cf_understatement>"CF's default format for queries is very ... non-standard." </cf_understatement>

Shawn Over a year ago

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.

SOS Over a year ago

@Shawn - I didn't think S.O. would appreciate my real thoughts on the default format, so I was being ... "tactful" ;-)

|

Collectives™ on Stack Overflow

Populate HTML Table Data from CFC Query Results

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related