CODEWITHSUNDEEP
sqlstructcoldfusionstructure
espresso_coffee
espresso_coffee

Reputation: 6110

Build struct and then populate with the values from query?

I have task to update/insert some fields in two different tables. Before I run my update I have to grab values from my query and put them in structure where one of my values should be the key. My query looks like this:

<cfquery name="getRecords" datasource="test">
    Select 
         s.ID
         ,f.USER_ID
         ,s.USER_NUMBER
         ,s.STATUS
         ,f.DINING 
    From USERS s
         Left Outer Join FIELDS f ON s.ID = f.USER_ID   
</cfquery>

I need USER_NUMBER to use as a key in my structure and store all other values from the query above. I will use this structure to compare values from my other list and then build final list that I will use for update/insert. I tried something like the code below, but it did not work:

Here is a stand alone example using a manual query:

<cfset getRecords = queryNew("")>
<cfset queryAddColumn(getRecords, "ID", [1,2,3])>
<cfset queryAddColumn(getRecords, "USER_ID", ["userA","userB","userC"])>
<cfset queryAddColumn(getRecords, "STATUS", ["Active","Active","Active"])>
<cfset queryAddColumn(getRecords, "DINING", ["X","Y","Z"])>

<cfset myStruct = StructNew()>

<cfloop query="getRecords">
    <cfset myStruct = [key:#USER_NUMBER#{
                                         id:#ID#
                                         ,userid:##USER_ID
                                         ,status:#STATUS#
                                         ,dining:#DINING#
                                        }]>
</cfloop>

If anyone can help with this code please let me know. I usually use arrays but this time I have to use struct because of some other reasons. Thank you.

Upvotes: 3

Views: 234

Answers (1)

Jedihomer Townend
Jedihomer Townend

Reputation: 400

I believe you just need to move your key up a level so that the loop doesn't overwrite the values. so try something like:

<cfset myStruct = StructNew()>

<cfloop query="getRecords">
    <cfset myStruct[getRecords.USER_ID] = {
        id:getRecords.ID,
        userid:getRecords.USER_ID,
        status:getRecords.STATUS,
        dining:getRecords.DINING
    }>
</cfloop>

Then to access the variables you can use something like:

<cfoutput>#htmlEditFormat(myStruct[1].dining)#</cfoutput>

Upvotes: 3

Related Questions