Two values from a form

Get Started
1

I wonder if this is possible and how it should be done.

I have the following form in a page:


<cfoutput>
<form action="" method="post">
<select name="cativity" size="4" multiple="multiple">
<option value="0" selected="selected">Select</option>
<cfloop query="getActivities">
<option value="#activity_id#, #activity_eng#">#activity_eng#</option>
</cfloop>
</select>
<input name="submit_button" type="submit" value="submit" />
</form>
</cfoutput>

As you can see does the option value hold both the activity_id and the activity_name. That is because I need both values but in different tables. The activity_id should go in to a relational table. When there would be only the activity_id value I could use a loop to do add the values to that relational table:


<cfloop index="ListElement" list="#Form.activity#" delimiters=",">
		<cfquery name="insertAmenities" datasource="#Application.dsn#">
			INSERT INTO company_activities
			( company_id, activity_id )
			VALUES
			(
			<cfqueryparam cfsqltype = "cf_sql_integer" value = "#Val( session.company )#" >
			,<cfqueryparam cfsqltype="cf_sql_integer" value="#Val( ListElement )#"/>
			)
		</cfquery>
		</cfloop>

But I need the string values from the selected activities as well as stated.
Those selected values should go as a comma delimited list in a varchar field the companies table.

To come back to the question. Is it possible to separate these values somehow, Or do I need javascript to do this?

Thanks in advance

2

I think you’d want to not loop through the form values.

Get the values from your form field:

formField.Value = “aa,bb”

Use some string functions to get the value before the comma and then get the value after the comma.

val1 = aa
val2 = bb

Use two separate queries to insert the data into the two tables

3

I found a solution. I think, or actually I’m sure, this is not the most elegant way of doing things, but it’s working and, for the moment, that is what count for me. Here’s is how I’ve done things.


<cfoutput>
<form action="" method="post">
<!--- All company related form fields are above --->
<select name="cativity" size="4" multiple="multiple">
<option value="0" selected="selected">Select</option>
<cfloop query="getActivities">
<option value="#activity_id#">#activity_eng#</option>
</cfloop>
</select>
<input name="submit_button" type="submit" value="submit" />
</form>
</cfoutput>

Insert companies and company_activities:


<cftransaction>
    <cfquery  datasource="#Application.dsn#">
    	INSERT INTO companies
       ( company_name, other, company, related, info, except, activity_eng)
       VALUES
       (
         <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( Form.company_name )#" />
        ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( other info )#" />
        ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( other info )#" />
        ,<!--- etc --->
       )
    </cfquery>
 <cfquery name="getLastID" datasource="#Application.dsn#">
    	SELECT LAST_INSERT_ID() AS lastID
        FROM companies
    </cfquery>
    <cfset session.company = getLastID.lastID />
	<cfloop index="ListElement" list="#Form.activity_id#" delimiters=",">
		<cfquery name="insertAmenities" datasource="#Application.dsn#">
			INSERT INTO company_activities
			( company_id, activity_id )
			VALUES
			(
			 <cfqueryparam cfsqltype = "cf_sql_integer" value = "#Val( session.company )#" >
			,<cfqueryparam cfsqltype="cf_sql_integer" value="#Val( ListElement )#"/>
			)
		</cfquery>
	</cfloop>

After that it was a matter to get just the inserted activity information so I queried the company_activities table based on the current session, converted those values into a list and updated the companies table field activity_eng with that list:


    	<cfquery name="getActivitylist" datasource="#Application.dsn#"> 
			SELECT a.activity_eng
        	FROM company_activities ca     
    		INNER JOIN activities a ON ca.activity_id = a.activity_id
        	WHERE company_id IN ( <cfqueryparam cfsqltype = "cf_sql_integer" value = "#Val( session.company )#" > )
    	</cfquery>    
        <cfset activityList = valueList( getActivitylist.activity_eng, ", " ) />
        
    	<cfquery datasource="#Application.dsn#">
    		UPDATE companies
        	SET
        	activity_eng = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim( activityList )#" />
        	WHERE company_id IN ( <cfqueryparam cfsqltype = "cf_sql_integer" value = "#Val( session.company )#" > )
    	</cfquery>
            
</cftransaction>

Again, this might be not the best way of doing it, but as long as it is working and no one can give me a better solution, i’m happy