two select advice

[quiero mas]

Hi Am i on the right track
I want to make a two related select between area and language
The user chooses the area then in the second drop down menu the available
language appears for that area.

Example: Area ---->Central
Language-----> English

Russian
-
SUBMIT- List of available hospital in that area

Question 1: Have I set up my tables correctly?

Table 1
Location_ID Area Area_Code
1 ?????? ??
2 ?????????Ku ??
3 ????????? ??
4 ???????? ??

Table 2

Language_id Language_name Languagecode
1 ??????? eng
2 ??????? rus
3 ??????? spn
4 ?????????? prt
5 ?????? ???
6 ??????? chn
7 ??????? ???

Table 3

Hospital_ID Hospital_name Area_Code Language
1 ?hospital ?? English
2 ?hospital ?? English
3 ?hospital ?? Russian

Question 2: I was thinking of following this example that i found - is it
appropriate?


<!--- Select the states and area codes. --->
<cfquery datasource="States" name="GetStates">
SELECT S.State, S.StateCode, AC.AreaCode
FROM States S, AreaCodes AC
WHERE S.StateCode=AC.StateCode
ORDER BY S.State, AC.AreaCode
</cfquery>

<!--- Select all the area codes. --->
<cfquery datasource="States" name="GetCodes">
SELECT AreaCode
FROM AreaCodes
ORDER BY AreaCode
</cfquery>

<script language = "JavaScript">
<!--
// For each state, create an array to hold the area codes.
// Each state array will be identified by the two-character state abbreviation
<cfoutput query="GetStates" group="State">
// Create the array
StateArray#StateCode# = new Array();
<cfset i = 0>
// Populate the array
<cfoutput>
<cfset i = i + 1>
StateArray#StateCode#[#i#] = #AreaCode#;
</cfoutput>
</cfoutput>

// Function to populate the area codes for the state selected
function PopulateAreaCode() {
// Only process the function if the first item is not selected.
if (document.StateForm.StateCode.selectedIndex != 0) {
// Find the state abbreviation
var ThisState =
document.StateForm.StateCode[document.StateForm.StateCode.selectedIndex].value;
// Set the length of the arecode drop down equal to the length of the
state's array
document.StateForm.AreaCode.length = eval("StateArray" + ThisState +
".length");
// Put 'Select' as the first option in the area code drop-down
document.StateForm.AreaCode[0].value = "";
document.StateForm.AreaCode[0].text = "Select";
document.StateForm.AreaCode[0].selected = true;
// Loop through the state's array and populate the area code drop down.
for (i=1; i<eval("StateArray" + ThisState + ".length"); i++) {
document.StateForm.AreaCode.value = eval("StateArray" + ThisState +
"");
document.StateForm.AreaCode.text = eval("StateArray" + ThisState +
"");
}
}
}
//-->


<form name="StateForm">
<p>
<table border="0">
<tr>
<td><b>State</b></td>
<td><b>Area Code</b></td>
</tr>
<tr>
<td>
<select name="StateCode" onChange="PopulateAreaCode()">
<option value="0">Select State
<cfoutput query="GetStates" group="State">
<option value="#StateCode#">#State#
</cfoutput>
</select>
</td>
<td>
<select name="AreaCode" width="70" style="width:150" size="1">
<option value="0">Select Area Code
<cfoutput query="GetCodes">
<option value="#AreaCode#">#AreaCode#
</cfoutput>
</select>
</td>
</tr>
</table>
</p>
</form>

[Dan Bracuk]

The way you have your tables set up, each hospital can have only one language.
Is that what you want?

Also, in table 3, I would use locationid rather than areacode as a field, and
have a foreign key relationship to location.

Also, I am not sure if your areacode field in location serves any useful
purpose. For your drop down list, you can have a value of locationid and
display the area.

[quiero mas]

Thanks for the Advice Dan - Im still working on the sub string code as well - so might be posting some questions about that in the near future.

Thanks again
Mark

[quiero mas]

I have been trying to create a two select but
Something isn't quite working.

Firstly im trying to populate the menus - but i get an error - page cannot be
displayed
first menu is location which in turn populates the second menu language then
this will be used for a query.
First i would like to get the menu populating



Hospital_ID Hospital_Name Location_ID
1 ????? 1
2 ????? 1
3 ????? 4

Hospital_ID Language_ID
1 1
1 2
1 6
2 1
2 6
3 2
3 1

Language_IDLanguage_nameLanguage_code
1 ??????? eng
2 ??????? rus
3 ??????? spn
4 ?????????? ???
5 ?????? ???
6 ??????? ???
7 ??????? ???

Location_ID Area
1 ?????
2 ?????????
3 ???????
4 ??????

?????????( I have tried to follow an example i found on the net)


<cfquery name="GetLocation" datasource="simple">
SELECT tslocation.Area, tslanguage.Language_name
FROM tslocation INNER JOIN (tslanguage INNER JOIN (tsHospital INNER JOIN
tshsptlang ON tsHospital.Hospital_ID = tshsptlang.Hospital_ID) ON
tslanguage.Language_ID = tshsptlang.Language_ID) ON tslocation.Location_ID =
tsHospital.Location_ID</cfquery>

<!--- Select all the area . --->
<cfquery datasource="simple" name="GetLanguage">
SELECT Language_name
FROM tslanguage
ORDER BY Language_name</cfquery>

<script language = "JavaScript">
<!--
// For each Area, create an array to hold the Area.
// Each Area array will be identified by the Area
<cfoutput query="GetLocation" group="Location">
// Create the array
AreaArray#Area# = new Array();
<cfset i = 0>
// Populate the array
<cfoutput>
<cfset i = i + 1>
AreaArray#Area#[#i#] = #Language#;
</cfoutput>
</cfoutput>

// Function to populate the Language for the Location selected
function PopulateLanguage() {
// Only process the function if the first item is not selected.
if (document.LocationForm.Location_ID.selectedIndex != 0) {
// Find the Location
var ThisLocation =
document.LocationForm.Location_ID[document.LocationForm.Location_ID.selectedInde
x].value;
// Set the length of the arecode drop down equal to the length of the
state's array
document.LocationForm.Language.length = eval("LocationArray" +
ThisLocation + ".length");
// Put 'Select' as the first option in the area code drop-down
document.LocationForm.Area[0].value = "";
document.LocationForm.Area[0].text = "Select";
document.LocationForm.Area[0].selected = true;
// Loop through the state's array and populate the area code drop down.
for (i=1; i<eval("LocationArray" + ThisLocation + ".length"); i++) {
document.LocationForm.Area.value = eval("LocationArray" +
ThisLocation + "");
document.LocationForm.Area.text = eval("LocationArray" + ThisLocation
+ "");
}
}
}
//-->
</script>



<form name="LocationForm">
<p>
<table border="0">
<tr>
<td><b>Location</b></td>
<td><b>Language</b></td>
</tr>
<tr>
<td>
<select name="Location" onChange="PopulateLanguage()">
<option value="0">Select Location
<cfoutput query="GetLocation" group="Location">
<option value="#Location#">#Location#
</cfoutput>
</select>
</td>
<td>
<select name="Language" width="70" style="width:150" size="1">
<option value="0">Select Location
<cfoutput query="GetLanguage">
<option value="#Language#">#Language#
</cfoutput>
</select>
</td>
</tr>
</table>
</p>
</form>