CODEWITHSUNDEEP
arrayscoldfusion
toleolu
toleolu

Reputation: 77

Array contents as a variable

Noob question here, I've got all this data in an array, but I can't figure out how to do anything with it. I need to use one element in a SQL update query, the other element is just for display purposes only. This works:

<cfset RecID=[]>
<cfset DistListNames=[]>    
<cfloop list="#form.ListName#" index="listcount">    
  <cfset arrayAppend(RecID, listFirst(listcount,":"))>
  <cfset arrayAppend(DistListNames, listLast(listcount, ":"))>    
</cfloop>    
<cfdump var=#RecID#>    
<cfdump var=#DistListNames#>

But other than displaying those little green boxes on the action page, I can't do anything with it. How do I set variables from the contents of RecID and DistListNames?

UPDATE:

I ended up going with Dan's suggestion and using the form variable instead of the array, still having trouble getting my head around arrays.

My confirmation page works using this:

<cfloop list="#form.ListName#" index="listcount">    
<cfset session.DistListID=(listFirst(listcount,":"))>
<cfset DistListNames=(listLast(listcount, ":"))>    
<cfoutput>    
#session.DistListID# #DistListNames#,&nbsp;    
</cfoutput>    
</cfloop>

This gives me the list names on the confirmation page, I just have the RecID's in there now just for testing. So far so good, right?

When I click the "Yes" button to confirm, I run this loop to just output the RecID's to make sure it works before I do basically the same loop around a cfquery for the SQL update. (All this code is on the same page, I don't open another file as the action page.)

<cfloop list="#session.DistListID#" index="RecID">    
<cfoutput>#RecID#</cfoutput>    
</cfloop>

All I get is the RecID of the last item in the list. I know it's another noob question, but I promise I've been online reading all morning trying to figure this out, I've tried for="1" to="#ListLen(session.DistListID#" but that just gives me a 1. I'm just not getting it.

Thanks

Upvotes: 0

Views: 204

Answers (3)

Leigh
Leigh

Reputation: 28873

the update [sic] writes the user id and the recid of the list to a junction table, that's it

Then you do not need loops or arrays.

As mentioned on your other thread, storing both the "id" and "name" within the same form field is what is making things more complicated than necessary :) Simply store the "ID" (only) and you can avoid looping altogether. 

 <!--- changed field "name" to be more descriptive --->
 <input type="checkbox" name="RecIDList" value="#RecID#">

When the form is submitted, FORM.RecIDList will contain a comma delimited list of "RecID" values. Since the source of those values is a database table, simply feed those ID's into a SELECT statement. Then you can insert all of the selected records in a single query - without looping. Something along these lines (obviously add validation):

INSERT INTO tbl_DistListJunction (DistListID, userID)    
SELECT RecID
       , <cfqueryparam value="#session.listUser#" cfsqltype="cf_sql_varchar">
FROM   YourDistrictTable
WHERE  RecID IN 
       (
        <cfqueryparam value="#FORM.RecIDList#" cfsqltype="cf_sql_integer" list="true">
       )

NB:

  • "cf_sql_int" is not a valid type. The correct type is cf_sql_integer

Upvotes: 0

toleolu
toleolu

Reputation: 77

OK. After all that, here's what I ended up with:

<cfquery name="InsertList" datasource="HHSCIntra">    
<cfloop list=#session.DistListID# index="recids">    
INSERT INTO tbl_DistListJunction (DistListID, userID)    
VALUES (<cfqueryparam value="#recids#" cfsqltype="cf_sql_int">, <cfqueryparam value="#session.listuser#" cfsqltype="cf_sql_varchar">)    
</cfloop>    
</cfquery>

Suggestions and critique's always welcome.

Thanks again to all.

Upvotes: 0

dukedevil294
dukedevil294

Reputation: 1305

If you want to use a variable in a SQL statement you would use:

<cfquery name="myQuery" datasource="myDatasource">
  UPDATE table_name 
  SET column_name = <cfqueryparam cfsqltype="cf_sql_varchar" value="#RecID[1]#">
  WHERE...
</cfquery>

To just display an array value to the page:

<cfoutput>#DistListNames[1]#</cfoutput>

To store a value in an array in a CF variable

<cfset myVar = DistListNames[1]>

Remember that CF arrays use a 1 based index so if you try to use DistListNames[0] you'll get an error.

Upvotes: 1

Related Questions