• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

need both name and ID of country in form

I have a form where the user selects his country. When submitting the form I need the CountrID to be inserted into a table and need the country name to be used in a cfmail.

my combobox looks like this:

<select name="Country" class=formcell id="Country"
                  <cfif isdefined ("form.Country") And #form.Country# IS NOT "" And #form.Country# IS NOT "#variables.FieldLabels['81']#">
                    <cfloop query="getcountry">
                      <option value='#getcountry.mycountry#' <cfif getcountry.mycountry eq form.Country>selected="true"</cfif>>#getcountry.mycountry#</option>
                    </cfloop>
                    <cfelse>
                    <option>#variables.FieldLabels['81']#</option>
                    <cfloop query="getcountry">
                      <option value='#getcountry.mycountry#'>#getcountry.mycountry#</option>
                    </cfloop>
                  </cfif>
                </select>  

from this I get the name of the country when I use: form.Country
how would I get the CountryID from this?   ....my query is below
         
<cfquery name="getcountry" datasource="#application.DS#" username="#application.dbuser#" password="#application.dbpass#">
SELECT     English AS mycountry, Priority, CountryID
FROM         tblCountries
ORDER BY Priority, mycountry
</cfquery>

Open in new window

0
Shawn
Asked:
Shawn
  • 4
  • 2
1 Solution
 
_agx_Commented:
So ... #form.Country# stores the name and you want the ID? Just run a query based on name

INSERT INTO OtherTable ( CountryID )
SELECT     CountryID
FROM         tblCountries
WHERE      YourCountryNameField = <cfqueryparam value="#form.country#" cfsqltype="cf_sql_varchar">

0
 
ShawnAuthor Commented:
I was thinking I might be able to store the id and the name in the combo box as the actual country name changes depending on the language setting (eg french/english)
0
 
_agx_Commented:
It's simpler to just make the ID part of the value

     <option value="#country#|#mycountry#'>#getcountry.mycountry#</option>

Then extract it with list functions

    <cfset countryID = listFirst(form.country, "|")>
    <cfset countryName = listRest(form.country, "|")>
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
_agx_Commented:
<option value="#country#|#mycountry#'>

Typo correction:   <option value="#countryID#|#mycountry#'>

Personally, I'd just store the ID (only). Then lookup the name via query on the action page.  But that's up to you.
0
 
_agx_Commented:
It's simpler to just make the ID part of the value

Failing that, you could make it an attribute of the <option>.  Then update a hidden field onChange.  Assuming it always fires in your form.

<script type="text/javascript">
	function storeCountryID() {
		var source = document.getElementById('country');
		var opt  = source.options[source.selectedIndex];
		var target = document.getElementById('countryID');
		target.value = opt.getAttribute("countryID");
	}
</script>
....
<select id="country" name="country" onChange="storeCountryID()">
	<option value="">select a country</option>
	<cfoutput query="getcountry">
		<option value="#mycountry#" countryID="#countryID#">#mycountry#</option>
	</cfoutput>	
</select>

<!--- make this field hidden --->
<input type="text" id="countryID" name="countryID" value="" />
....

Open in new window

0
 
ShawnAuthor Commented:
nice, thanks agx
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now