Alright.. this can be done with javascript and with php, among other languages.
I will show you in PHP since that is how I did it. I use PostgreSQl for my database. If you use something different, you will need to use the appropriate php-database commands.
I personally would reconstruct your database to include 1 table for this.. 1st column be state.. 2nd column be country, preferably some sort of code or abbrev. I have a similar setup.. I have college names in one column.. and the 2 letter state abbrev in the next column. So it is very easy for me to do a query on that table that...
select * from schools WHERE state='NY'
it would be nice for you to be able to..
select * from states WHERE country='USA'
This code will do the following.. Show the user a dropdown box of countries.. then after they select their country and hit submit.. it will then refresh the form, using the same php page and show them a list of states in that country.
Notice I am not pulling the Countries out of the database, I am only pulling the states out. I wanted to show you both ways of doing this. You can easily change thec country section to match the state section(pull the values out of the database) or change the state section to match the country section (hard code the selections in the code).
if (!$usercountry) {
echo "<FORM ACTION=\"$PHP_SELF\" METHOD=\"POST\">";
echo "<SELECT NAME=\"usercountry\">
<option selected value=''>Select country</option>
<option value='CA'>Canada</option>
<option value='US'>UnitedStates</option>
</select>";
echo '<INPUT TYPE="Submit" NAME="submit" VALUE="Submit">
<INPUT TYPE="Submit" NAME="resetform" VALUE="Reset">
</FORM>';
}
if ($usercountry && $submit && $state ) {
echo 'Yay, it worked, ';
}
if ($usercountry && !$submit) {
echo "<FORM ACTION=\"$PHP_SELF\" METHOD=\"POST\">";
echo "<select name=\"userstate\">
<option value="">Select State</option>";
$query = "SELECT * FROM states WHERE state = '$usercountry'";
$result = pg_exec($db, $query);
while($row = @pg_fetch_row($result, $i++)) {
$state_result = $row[1];
print("<OPTION value=\"$state_result\">$state_result</OPTION>");
echo '<INPUT TYPE="Submit" NAME="submit" VALUE="Submit">
<INPUT TYPE="Submit" NAME="resetform" VALUE="Reset">';
}
echo '</FORM>';
}
Of course there may be a few errors with ()'s, ;'s, ' vs ", etc, and it may not look to pretty, but it works for me.
