Obtain Multiple Form values and Insert

[reya276]

Hello,
I'm trying to insert multiple value list passed through a form, but I can only accquired only one list. what I mean is I'm trying to pass two list on two different values as follow:

ipa_code_ = "228,420,457"
payor_code_ = "WC,HU,DMG,PC,VA"

and below is the code that I'm trying to use to accomplish this:

<cfif (username) NEQ "" AND (ipa_id_) NEQ "">
<!---This loops through the ipa codes and inserts a record for each line--->
<cfloop index="curLine" list="#ipa_id_#">
<cfquery datasource="#request.dsn#">
INSERT INTO Assign_ipa (ipa_id,hmoid,userid,username,PayorCode)
VALUES ('#curLine#','#hmoid#','#userid#','#username#','#PayorCode#')
</cfquery>
</cfloop>
<!---This tries to update the previously inserted values from above but it does not work, is trying to update the records with the payor codes---->
<cfloop index="curLine" list="#PayorCode_#">
<cfquery datasource="#request.dsn#">
UPDATE Assign_ipa SET PayorCode = '#curLine#'
WHERE userid = '#userid#'
</cfquery>
</cfloop>
<cfset msg = "You successfully assigned IPAs to ("&#username#&")!">
<cfelse>
<cfset msg = "You NOT assigned IPAs to the users!">
</cfif>

So as you can see from the code above it will not work because every time I try to update the records only one value from the list is taken and updated.

ipa_code hmoID userid username payor_code
228 1 41 reya10276 VA
420 1 41 reya10276 VA
457 1 41 reya10276 VA

Any ideas on how to accomplish this would be greatly appreciated, thanks.

[Dan_Bracuk]

Your intent is not clear. If you start with these two lists,
ipa_code_ = "228,420,457"
payor_code_ = "WC,HU,DMG,PC,VA"

what should your db records look like?

[reya276]

My records should look like this:

228 1 41 reya10276 WC
420 1 41 reya10276 HU
457 1 41 reya10276 DMG

As oppose to what they are now which is:

228 1 41 reya10276 VA
420 1 41 reya10276 VA
457 1 41 reya10276 VA

What I need to do is select the IPAs(codes) and at the same time grab it's payorCode. Every IPA code has a payorCode so I just want to make sure it gets inserted along with the ipacode, so what I tried to do is just pass it as a hidden field but what I get is this:

228 1 41 reya10276 WC,HU,DMG
420 1 41 reya10276 WC,HU,DMG
457 1 41 reya10276 WC,HU,DMG

Since it's only doing the looping on the list of ipa_codes not the payorCodes
<cfloop index="curLine" list="#ipa_code_#">

[Dan_Bracuk]

If you start with these two lists,
ipa_code_ = "228,420,457"
payor_code_ = "WC,HU,DMG,PC,VA"

and you want to end up with this:
228 1 41 reya10276 WC
420 1 41 reya10276 HU
457 1 41 reya10276 DMG

Do a loop from 1 to the listlen of ipa_code. Use the listgetat function inside that loop.

[reya276]

Thank you so much that worked perfectly.

<cfloop From = "1" TO= "#ListLen(ipa_id_)#" INDEX="Counter">
<cfset ipa_id = #ListGetAt(ipa_id_, Counter)#>
<cfset payorCode = #ListGetAt(payorCode_, Counter)#>
<cfquery datasource="#request.dsn#">
INSERT INTO Table(ipa_id,hmoid,userid,username,PayorCode)
VALUES ('#ipa_id#','#hmoid#','#userid#','#username#','#payorCode#')
</cfquery>
</cfloop>