Cycle through list and build compound array...

[Guest]

I have a database that I'm pulling data from that is already existing. There is a designs table and a details table. The designs table stores the id's of the details items that are associated with the design. Since I'm using Flash remoting I would like to just get all the designs and their details back in one compound array instead of making a bunch of calls. I'm stuck on how to make the string "5,8,12,19" into an array, cycle through the array and then get the details info for each id. Then return the compound array when finished.

I would prefer the array be like this:

[design item 1 and it's data][details array of the design 1 items], [design item 2 and it's data][details array of the design 2 items]....

Here is what I have so far

<cffunction name="getDesignsByAct" access="remote" returntype="query" hint="gets activities by id">
<cfargument name="send_id" type="numeric" required="yes" />
    <cfquery name="getDesignsByActQuery" datasource="#dsn#">
    SELECT design_id, design_items
    FROM designs_table
       
    WHERE design_activity = #send_id#
    </cfquery>
    <cfquery name="getDetailsQuery" datasource="#dsn#">
SELECT
     *
    FROM
     details_table
   
    WHERE <!-- cycle through the list of "design_items" in the above query -->
   
</cfquery>
    <cfset this_return[1] = getDesignsByActQuery>
<cfset this_return[2] = getDetailsQuery>

   <cfreturn this_return >
</cffunction>

Thanks in advance.

[Dan_Bracuk]

Your query becomes

select the fields you need

from designs_table join details_table on something

where design_activity = #send_id#

Then loop though it to populate your array.  Use the currentrow variable as your array row numbers.

[BKBK]

SELECT *
FROM details_table
WHERE item in (#valuelist(getDesignsByActQuery.design_items)#)

[BKBK]

Cycle through list and build compound array

Oh, there's also listToArray

[insuractive]

Is your end goal to get a multi-dimensional array?  a query recordset object?  or an array of structs?  Based on your description, it looks like you want something like this:

arrMultiDimArray = NewArray(2)

arrMultiDimArray[1][1] = Query Data from Design Record 1

ArrMultiDimArray[1][2] = Array of Detail records data structures (or query recordset object?) associated with design record 1

If that is the case, then you should be able to put something together like this:

1) Query your design & details data from the database as a joined recordset

2) Initialize your multi dimensional array

3) You can use the <cfoutput group=""> attribute to create an outer loop that only changes once for each new design ID

4) Increment your first dimension array and add your design data to position one of your 2nd dimension

5) Create a new array to store your Detail record items

6) Use a nested <cfoutput> loop to loop over your detail item records and populate your new array

7) Store your new array in position two of your 2nd dimension

8) Return your 2D array to your flash remoting app