CF - submit two values with select box

JohnMac328
JohnMac328 used Ask the Experts™
on
I need a select box to enter two values - it is a re-write from a asp program.  For example I can get the value of 10 to go into the correct field in the table but I also need the description to go into the description field.  I am testing with one select box but will have three total that can be chosen from.

 <cfselect name="gift_preset10">
              <option value="">Pick One</option>
              <option value="10">$10 Regal Movie Card</option>
              <option value="10">$10 Santikos Movie Card</option>
              <option value="10">$10 Target Gift Card</option>
              <option value="10">$10 Walmart Gift Card</option>
              <option value="10">$10 HEB Gift Card</option>
              </cfselect>

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I woudl say you have to change your value="" to something distinct so you can distinguish the values from one another.  

Since only the VALUE comes over in the form field, each option has to have a different value for you to be able to know which one was selected.

The ideal way to do this is to pull those values from a database and put the Primary key ID from teh record into the options' value.

If you don't have them in the database, you can append a code like this...

value="10:Regal"
value="10:Santikos"
value="10:Target"

... then just parse out the result
Pravin AsarPrincipal Systems Engineer
Top Expert 2005

Commented:
You can use bind to update the form field.

Look at the example below. Passing the same value for different options does not make sense. Hence make a description a part of value.

Once form is submitted, process the field (it is matter of using ListToArray function)

<cfform name="f1">
<cfselect name="gift_preset10">
              <option value="" selected>Pick One</option>
              <option value="10:$10 Regal Movie Card">$10 Regal Movie Card</option>
              <option value="10:$10 Santikos Movie Card">$10 Santikos Movie Card</option>
              <option value="10:$10 Target Gift Card">$10 Target Gift Card</option>
              <option value="10:$10 Walmart Gift Card">$10 Walmart Gift Card</option>
              <option value="10:$10 HEB Gift Card">$10 HEB Gift Card</option>
</cfselect>
<br/>
<cfinput type="text" bind="{f1:gift_preset10}" name="value1" >
</cfform>

Author

Commented:
I guess that was the main point of my question - how to parse out the two values I need in the parameter value for the insert statement

<cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer">,
      <cfqueryparam value="#form.gift_preset10#" cfsqltype="CF_SQL_VARCHAR">
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Pravin AsarPrincipal Systems Engineer
Top Expert 2005

Commented:
As I wrote in previous post , use ListToArray

e.g.

<cfset values=ListToArray("10:$10 Regal Movie Card",":")>

Author

Commented:
I went a head and made the table - I am getting a strange message even though I am using this format in another program

<cfquery name="10dollar" datasource="data">
SELECT     ID, award_desc, award_cost
FROM         gift_present10
</cfquery>  

and the select

     <cfselect name="gift_present10"><cfoutput query="10dollar">
            <option value="#ID#">#award_desc#</option>
            </cfoutput>
            </cfselect>

and the error

Invalid tag nesting configuration.  
A query driven cfoutput tag is nested inside a cfoutput tag that also has a query attribute. This is not allowed. Nesting these tags implies that you want to use grouped processing. However, only the top-level tag can specify the query that drives the processing.  
 
The error occurred in C:\inetpub\wwwroot\maxibillion_CF\Buy_Prize.cfm: line 72
 
70 :            <cfoutput>#strError#</cfoutput>
71 :               <cfelse>
72 :             <cfselect name="gift_present10"><cfoutput query="10dollar">

Author

Commented:
Wait - had to remove an upper query - how does this now get the award_cost into the table

   <cfquery name="10dollar" datasource="IntranetDB_Coldfusion">
SELECT     ID, award_desc, award_cost
FROM         gift_present10
</cfquery>  


 <cfselect name="gift_present10"><cfoutput query="10dollar">
            <option value="#ID#">#award_desc#</option>
            </cfoutput>
            </cfselect>

  INSERT INTO prize_records (purchaser, award_desc,award_cost)
    VALUES
    (
      <cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer">,
      <cfqueryparam value="#form.gift_present10#" cfsqltype="CF_SQL_VARCHAR">

Author

Commented:
pravinasar

with your example in ID: 37999389 - how would the insert statement look with the   <cfqueryparam value="#form.gift_present10#" cfsqltype="CF_SQL_VARCHAR">  etc?
Most Valuable Expert 2015
Commented:
John, if you're now storing the values in a table, the "ID" column should be some sort of unique auto incrementing number.  Just use that value to lookup and insert whatever columns you need:

 INSERT INTO prize_records (purchaser, award_desc,award_cost)
 SELECT  <cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer">
              , award_desc
              , award_cost
 FROM    gift_present10
WHERE ID = <cfqueryparam value="#form.gift_present10#" cfsqltype="cf_sql_integer">

Though unless you're deliberately storing a snapshot, you wouldn't normally store the extra values. Just the award "ID" :

      Table: prize_records  Columns:  purchaser_id, gift_id

... and use a JOIN whenever you need to display the amount or description.

ie
SELECT pr.purchaser, g.award_desc, g.award_cost
FROM   prize_records pr INNER JOIN gift_present10 g
                  ON g.id = pr.gift_id
....

Author

Commented:
agx - I did not know a select statement could go with a <cfqueryparam value - I am getting a syntax error - I tried moving things around and playing with the commas but it still displays the error

Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'SELECT'.  
 
The error occurred in C:\inetpub\wwwroot\maxibillion_CF\Buy_Prize.cfm: line 36
 
34 :               award_desc, award_cost
35 :  FROM    gift_present10
36 : WHERE ID = <cfqueryparam value="#form.gift_present10#" cfsqltype="cf_sql_integer">


INSERT INTO prize_records (purchaser, award_desc,award_cost)
 SELECT  <cfqueryparam value="#form.employeeID#" cfsqltype="cf_sql_integer">
              , award_desc
              , award_cost
 FROM    gift_present10
WHERE ID = <cfqueryparam value="#form.gift_present10#" cfsqltype="cf_sql_integer">

Author

Commented:
Wait - got it

Author

Commented:
Looks like I will need 3 forms total - can I assume that this will work

<cfif structKeyExists(FORM, "submitButtonForm1")>
<cfif structKeyExists(FORM, "submitButtonForm2")>
<cfif structKeyExists(FORM, "submitButtonForm3")>

and of course each form button is named as above?
To agx's point, all you need to do is save the ID from your new table.  No need to copy the award description and amount into the prize_records table.

 INSERT INTO prize_records (purchaser, award_id)
 Values (
   #val(form.employeeID)#
  ,#val(form.gift_present10)#
 )


However, if you want a historic snapshot, you may choose to denormalize your prize records table and store the amount and description.   An example of this would be a shopping cart.  A person adds a product on Jan 1 at $10.  On Feb 1, the price changes to $12, you don't want the price to change on all the old orders, so you have to store the $10 price in the orders along with the product name and qty.

This may be true for your situation if the values may change.

The alternative is that if the $10 price will change to a $20 prize, then just disable the $10 prize in the gift_present10 table so it is no longer seen and create a new $20 record in that table to use.

I think this last approach would be the one you may want.   So, I think storing only the ID is the way to go.

One more tip:    It's easy to get lost trying to figure out what IDs go to what table.  I feel it's important to use a convention.  This is how most people do it.   When you create an ID in a table, call the ID the same as the table name with _ID after it.    So, your table gift_present10's ID would be something like   giftPresent10_ID, then use this same name whenever you have a foreign key that points to it.   So in prize_records, you would use the same name giftPresent10_ID.
You can certainly have this type of set up on your action page.  You can have three different forms, or you can even have three different buttons (with these names) in one form to do different actions depending on what the user clicked.

Looks like I will need 3 forms total - can I assume that this will work

<cfif structKeyExists(FORM, "submitButtonForm1")>
<cfif structKeyExists(FORM, "submitButtonForm2")>
<cfif structKeyExists(FORM, "submitButtonForm3")>

and of course each form button is named as above? 

Open in new window



However, I don't see how this fits in your situation.   It seems to me you simply want your user to select one value from your SELECT tag and store it in your table.

Author

Commented:
First I am recording the purchaser id, award_desc and award cost into the prize records table - that works for the acct dept.

The way it works now is there is a form displayed with $10, 15, 25 and 50

If you only have 5 then a message displays you don't have enough and no form displays

If you have 15 then you will see only the $10 prize box and so on.  I needed to have the different forms to restrict what to display based on how much they have

Author

Commented:
Or if there is a better way I am open to suggestions

Author

Commented:
Thanks again

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial