Coldfusion CFC Return JSON display in Jquery, how do I handle multiple records from CFC and display in Jquery?

Question

I need a way to grab the json return from coldfusion and display it in jquery I can display the first result fine, but if more that one record comes back I am stuck Here is my cfc

<cfquery name="users" datasource="#thedb#">
In this query I can get 1 record to 25 or even more results         
</cfquery>

Here is my out put to jquery, not sure if this is a good way to do this, but.... this is how I handle mutliple records right now.

<cfset var user = structNew()/>

<cfset thenumber = 1>   
        <cfloop query="users"> 
        <cfset user["newrequestor#theNumber#"] = users.requestor/>
        <cfset user["newrequestorusername#theNumber#"] = users.requestor_username/>
        <cfset user["newrequestorphone#theNumber#"] = users.requestorphone/>
        <cfset user["newrequestoremail#theNumber#"] = users.requestoremail/>
        <cfset user["newthedate#theNumber#"] = users.thedate/>
        <cfset user["newapproved#theNumber#"] = users.approved/>
        <cfset user["newcomments#theNumber#"] = users.comments/>
        <cfset user["newviewed#theNumber#"] = users.viewed/>
        <cfset thenumber = thenumber + 1>
        </cfloop>

<cfreturn user>

End of CFC

Here is my jquery, I have it manually set to grab first record... not sure how to loop over to get all records returned.

Here I pass my arguments to the cfc to get my result. works great

thedata = instance.getSearch($("#therequestor").val(), $("#fromDate").val(), $("#toDate").val(), $("#theapproved").val(), $("#theroom").val());

Next I build a row in jquery to handle my first record, manual setting not dynamic.

var new_Return = '<tr id="newReturn"><th style="text-align:left;" id="first">Request Date:</th><td>'+thedata.newthedate1+'</td><td>&nbsp&nbsp&nbsp&nbsp</td><th style="text-align:left;" id="first">Requestor:</th><td>'+thedata.newrequestor1+'</td><td>&nbsp&nbsp&nbsp&nbsp</td><th style="text-align:left;" id="first">Approved:</th><td>'+thedata.newapproved1+'</td><td>&nbsp&nbsp&nbsp&nbsp</td><th style="text-align:left;" id="first">Viewed:</th><td>'+thedata.newviewed1+'</td></tr>';
        $("#theReturnFormTable").append(new_Return)

Displays first result in my div tag great, how can I loop over if I have multiple records Example thedata.newrequestor1 is my first record

then I could have more many more

thedata.newrequestor2 is my 2nd thedata.newrequestor3 is my 3rd etc. on and on

How can I handle this in jquery, or do I have to start different in coldfusion cfc??

Confused.. "user" above is a struct not a query... so you will have a flat struct with "n" number of keys and each key name will contain a digit. This seems kind of kludgy to me (no offense). After all a query IS an array of structs - so it would have user[2]['viewed'] already in it... why would you need to reengineer it? — Mark A Kruger
– Mark A Kruger, Commented Apr 10, 2012 at 20:15
Similar to what Mark says I think you will find this easier if you use serializeJSON() on the query and then loop through that. — Sam Farmer
– Sam Farmer, Commented Apr 10, 2012 at 20:17
Not sure why I did, so I should skip the struct all together just return the query — user1253239
– user1253239, Commented Apr 10, 2012 at 20:18
You can simply return the query, just console.log it so that you can inspect it's structure and reference it properly. — user400654
– user400654, Commented Apr 10, 2012 at 20:19

Josh Siok · Accepted Answer · 2012-04-10 20:34:07Z

7

You could just call the remote function and let ColdFusion serialize the query object to JSON? (notice returnFormat="JSON")

<cffunction name="getUsers" access="remote" returnType="query" returnFormat="JSON">
   <cfquery name="users" datasource="#thedb#">
       In this query I can get 1 record to 25 or even more results         
   </cfquery>
   <cfreturn users>
</cffunction>

JSON formatting will look like this:

{"COLUMNS":["NEWREQUESTER","NEWREQUESTERUSERNAME"],"DATA":[["1","JOHN DOE"],["2","JIM DOE"]]}

answered Apr 10, 2012 at 20:34

Josh Siok

9364 silver badges8 bronze badges

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

Comments

user400654 · Accepted Answer · 2012-04-10 21:36:59Z

3

Store your users in an array and return that array.

<cfset var userArr = arrayNew(1)/>
<cfloop query="users">
    <cfset var user = structNew()/>
    <cfloop list="#users.columnlist#" index="column">
        <cfset user[column] = users[column]>
    </cfloop>
    <cfset ArrayAppend(userArr, user)/>
</cfloop>
<cfreturn userArr />

That creates an array of objects in javascript like this:

[
    {
        newrequestrr:"foobar",
        newrequestorusername:"foobar",
        ...
    },
    ...
]

Update: I made code a little more dynamic. I use a custom function in my udf library to make this conversion from query structure to array of rowstructs, I modified it to use your variables.

edited Apr 10, 2012 at 21:36

answered Apr 10, 2012 at 20:18

user400654

95.1k16 gold badges168 silver badges188 bronze badges

3 Comments

Mark A Kruger Over a year ago

Good answer but overkill. A query already is an array. Just serialize that bad boy :)

Aidan Kane Over a year ago

Isn't it in a weird format though? A struct of arrays rather than an array of structs?

user400654 Over a year ago

Yes, when you return it directly as a query, it is a structure of arrays rather than an array of structures. I prefer the array of structures.

Nathan Strutz · Accepted Answer · 2019-02-13 04:02:46Z

If you want something with the normal json structure, you are going to have to create it. If you're battling with CORS, PM me. I am using this on an Angular 7 front end. Remove the returnType="query" from your cffunction tag; it just returns the half baked quasi json format. Hope this helps someone.

<cffunction name="getRecipies" access="remote" returnFormat="JSON"

    <cfquery name="qryRecipies" datasource="#VARIABLES._dsnName#" username="#VARIABLES._dsnUsername#" password="#VARIABLES._dsnPassword#">
        SELECT *        
        FROM recipes
    </cfquery>

    <cfset prepArr = arrayNew(1)/>
    <cfloop query="qryRecipies">
        <cfset prep = structNew()>
        <cfset prep["id"] = qryRecipies.id>    
        <cfset prep["name"] = qryRecipies.name>
        <cfset prep["description"] = qryRecipies.description>
        <cfset prep["imagePath"] = qryRecipies.imagePath>
        <cfset ArrayAppend(prepArr, prep)/>
    </cfloop>

    <cfreturn serializeJSON(prepArr) />   
</cffunction>

Collectives™ on Stack Overflow

Coldfusion CFC Return JSON display in Jquery, how do I handle multiple records from CFC and display in Jquery?

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related