asked on

inserting form data into Data base using coldfusion 9

Hi, i have a simple form with few users and their role. I want to insert all selected roles into my data base table. I am not sure how to do that.

see the code below:

<form id="contactform" name="contactform" action="role_act.cfm" method="post" class="cssform">
<legend>Add Other Role(s):</legend>
<br>
<input type="hidden" name="course_id" value="#num#">

<cfloop query="qListAllPrgCont">
<div class="box_2">
<input type="hidden" name="contact_id" value="#qListAllPrgCont.id#">
#qListAllPrgCont.first_name# #qListAllPrgCont.last_name# :
<input type="checkbox" name="role_id" value="3">Moderator
<input type="checkbox" name="role_id" value="4">super
</div>
</cfloop>

<input type="submit" name="submit" value="Add Roles" class="button">
</form>

in my data base table i have a table named Contact_Role and i have fields as

Id | Contact_ID | Role_id |Course_ID

when i submit my form i want to insert these data one row at a time. so, i believe i need to construct some sort of array to hold these data. How can i do that?

khan02

ASKER

The form looks like,

Alen [] moderator []super
Mich [] moderator []super
Nick [] moderator []super

and the data base table is

Id | Contact_id | role_id | course_id

the id field is auto generated, contact_id is already in the form as a hidden field for each person and coming out from loop. and the course_id is already in my url parameter.

question is how to construct the code to insert form data into my table, one row at a time.

gdemaria

The roles being associated with the course AND contact_id adds a little complexity to it. You need to be sure to associate the contact with that contact's roles. Therefore, you need to add a row counter to the field name for contact_id and role_id. That way, contact_id1 will have roles role_id1 and contact_id2 will have roles in role_id2, etc...

This is your form..

    <cfloop query="qListAllPrgCont">
     <cfset Row = qListAllPrgCont.currentRow>
     <div class="box_2">
         <input type="hidden" name="contact_id#Row#" value="#qListAllPrgCont.id#">
         #qListAllPrgCont.first_name# #qListAllPrgCont.last_name# :
              <input type="checkbox" name="role_id#Row#" value="3">Moderator
              <input type="checkbox" name="role_id#Row#" value="4">super                  
      </div>
    </cfloop>

Open in new window

This is your action script...

    <cfloop index="kk" from="1" to="#form.rowCount#">
       <cfset contact_id = form["contact_id" & kk]>
       <cfparam name="form.role_id#kk#" default="">
       <cfset role_id = form["role_id" & kk]>
       
       Now you have contact_id
       and Role_id can be a list of roles, you have to add/remove multiple roles
    
    </cfloop>

Open in new window

There is another step involved inside the action, you then need to add/remove the one or many roles for each contact

RickEpnet

The simplest way in to use <cfinsert

<cfinsert dataSource = "cfdocexamples" tableName = "COMMENTS" formFields = "Email,FromUser,Subject,MessText,Posted">

This is not necessarily the best way to do this but it is the simplest.

You could also use a SQL query Insert Into

khan02

ASKER

form.rowCount is Undefined.....in action???

gdemaria

You need to create a hidden form to keep track of the total number of row variables you have..

<input type="hidden" name="rowCount" value="#Row#">

This line should go in your form after the end of the CFLOOP.

It will tell your action how many loops you have...

khan02

ASKER

There is another step involved inside the action, you then need to add/remove the one or many roles for each contact...."how do i do that"?

khan02

ASKER

i think inside the loop in action, i will have to do another loop to split the roles and somehow insert into DB.

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial