sql - Creating a survey using Coldfusion: Stuck on the table data insert

Question

I'm trying to create a survey tool in Coldfusion and I'm stuck on one part.

My tables are:

• t_forms (id, name, desc)
• t_questions (id, question, type, formid, order)
• t_cdata (id, email)
• t_cqdata (formid, questionid, customerid, answergiven)

The form fields are dynamically built using a url variable and look like this, for example:

<cfquery name="gs">
    select * from t_forms where id = #url.sid#
</cfquery>

<cfquery name="gq">
       select * from t_questions where fid = #gs.id# ORDER BY order ASC
</cfquery>

<cfform name="survey" method="post" action="">
    <cfloop query="gq">
       <cfinput type="text" name="q#gq.id#">
    </cfloop>
    <cfinput type="text" name="email">
    <cfinput type="hidden" name="fid" value="#url.fid#">
    <cfinput type="submit" name="submit" value="Save">
</cfform>

However, I'm having trouble when I need to put the value of the answer into the t_cqdata table, as the form element input needs to go into the table as well.

If anyone could help or point out where I am going wrong, that would be appreciated .

Answer 1

There is more than one way to have form fields associated with database identifier values. This is the one I find easiest to comprehend.

On the form page.

<cfoutput query="somequery">
<input name="field1#databaseID#">
<input name="field2#databaseID#">

etc

On the processing page.

<cfloop list="#form.fieldnames#" index="ThisElement">
<cfif left (ThisElement, 6) is "field1">
<cfset ThisID = RemoveChars(ThisElement, 1, 6)>
<cfset ThisField1Value = form[ThisElement]>
<cfset ThisField2Value = form['field2' & ThisID]>

Continue to set variables and then do something with them. In fact, in this example, once you've set ThisID, setting more variables is optional. You can simply use the form variables directly using the synax shown.