This will do exactly what you want:
<?
//Dynamic Dropdowns
//Craig Donnelly (Phirus) - [email]craig@evilwalrus.com[/email]
//Im not sure where the bones of this script came from, but I spent quite a bit of time extending & modifying it.
//Any Questions mail me..this script will populate your DB with the Info that I have put in. Easy to modify.
?>
<html>
<title>PHP/MySQL - Dynamic DropDowns</title>
<body>
<?
//Database Variables
$db_Database = "YOUR_DB_NAME";
$db_UserName = "YOUR_USERNAME";
$db_Password = "YOUR_PASSWORD";
$db_Hostname = "YOUR_HOSTNAME";
//Connect to the Database
mysql_connect($db_Hostname, $db_UserName, $db_Password) || UhOh("Can't Connect to Database: ".mysql_error());
mysql_select_db($db_Database);
$test = "SELECT * FROM tblCountry";
if (mysql_query($test)){
// If tables exist do nothing
}else{
// If they dont exist - create tables and fill in the data
$query[] =" CREATE TABLE tblCountry (id int(10) DEFAULT '0' NOT NULL auto_increment, CountryName varchar(25), PRIMARY KEY(id), KEY id (id))";
$query[] =" CREATE TABLE tblCity (id int(10) DEFAULT '0' NOT NULL auto_increment, CityName varchar(25), Countryid int(10), PRIMARY KEY(id), KEY id (id))";
// Create the 5 countries
$query[] = "INSERT INTO tblCountry VALUES( 1, 'United Kingdom')";
$query[] = "INSERT INTO tblCountry VALUES( 2, 'USA')";
$query[] = "INSERT INTO tblCountry VALUES( 3, 'Ireland')";
$query[] = "INSERT INTO tblCountry VALUES( 4, 'South Africa')";
$query[] = "INSERT INTO tblCountry VALUES( 5, 'Australia')";
//Create the 5 cities for the UK
$query[] = "INSERT INTO tblCity VALUES( 1, 'Cambridge',1)";
$query[] = "INSERT INTO tblCity VALUES( 2, 'London',1)";
$query[] = "INSERT INTO tblCity VALUES( 3, 'Bedford',1)";
$query[] = "INSERT INTO tblCity VALUES( 4, 'Manchester',1)";
$query[] = "INSERT INTO tblCity VALUES( 5, 'Brighton',1)";
//Create the 5 cities for the US
$query[] = "INSERT INTO tblCity VALUES( 6, 'New York',2)";
$query[] = "INSERT INTO tblCity VALUES( 7, 'Chicago',2)";
$query[] = "INSERT INTO tblCity VALUES( 8, 'Virginia',2)";
$query[] = "INSERT INTO tblCity VALUES( 9, 'Boston',2)";
$query[] = "INSERT INTO tblCity VALUES( 10, 'California',2)";
//Create the 5 cities for the Ireland
$query[] = "INSERT INTO tblCity VALUES( 11, 'Dublin',3)";
$query[] = "INSERT INTO tblCity VALUES( 12, 'Cork',3)";
$query[] = "INSERT INTO tblCity VALUES( 13, 'Limerick',3)";
$query[] = "INSERT INTO tblCity VALUES( 14, 'Kerry',3)";
$query[] = "INSERT INTO tblCity VALUES( 15, 'Galway',3)";
//Create the 5 cities for the South Africa
$query[] = "INSERT INTO tblCity VALUES( 16, 'Johannesburg',4)";
$query[] = "INSERT INTO tblCity VALUES( 17, 'Cape Town',4)";
$query[] = "INSERT INTO tblCity VALUES( 18, 'Port Elizabeth',4)";
$query[] = "INSERT INTO tblCity VALUES( 19, 'Durban',4)";
$query[] = "INSERT INTO tblCity VALUES( 20, 'George',4)";
//Create the 5 cities for the Oz
$query[] = "INSERT INTO tblCity VALUES( 21, 'Sydney',5)";
$query[] = "INSERT INTO tblCity VALUES( 22, 'Melbourne',5)";
$query[] = "INSERT INTO tblCity VALUES( 23, 'Canberra',5)";
$query[] = "INSERT INTO tblCity VALUES( 24, 'Perth',5)";
$query[] = "INSERT INTO tblCity VALUES( 25, 'Darwin',5)";
// While Loop to Insert Querys
while ($each_query = each($query))
{
$result = mysql_query($each_query[1]);
if (!$result){
print("<b>WARNING! We've encountered an error. Please check manually. Error: ".mysql_error())."<p>";
die();
}
}
}
echo "<form name=\"f1\" action='$PHP_SELF' method=\"post\">\n";
//read the database
$result = mysql_query("SELECT tblCountry.CountryName,tblCity.Countryid,tblCity.CityName,tblCity.id FROM tblCity,tblCountry WHERE tblCity.Countryid=tblCountry.id");
//write the table
echo "<table width=\"200\" border=\"0\" cellpadding=\"5\" cellspacing=\"5\">";
// write the country's listbox...
echo "<tr><td valign=\"middle\" align=\"center\"><font color=\"#000000\" face=\"verdana,arial,helvetica\" size=\"2\">Country</font></td><td><select name=\"country\" size=\"1\" onchange=\"countryselected(this);\">\n";
// write the entry code for the javascript...\n is used to force a new line so the resultant code is more readable
$sJavaScript = "function countryselected(elem){\n for (var i = document.f1.city.options.length; i >= 0; i--){ \n document.f1.city.options[i] = null;\n";
// loop through the database..
$sLastCountry="";
echo "<option value=\"#\" selected>Please Select</option>";
while ( $row = mysql_fetch_array($result))
{
// is this a new country?
If ($sLastCountry!=$row["CountryName"]){
// if yes, add the entry to the countrys listbox
$sLastCountry = $row["CountryName"];
echo "\n<option value='".$row["Countryid"]."'>".$sLastCountry."</option>";
// and add a new section to the javascript...
$sJavaScript = $sJavaScript."}\n"."if (elem.options[elem.selectedIndex].value==".$row["Countryid"]."){\n";
}
// and add a new city line to the javascript
$sJavaScript = $sJavaScript."document.f1.city.options[document.f1.city.options.length] = new Option('".$row["CityName"]."','".$row["id"]."');\n";
}
// finish the country's listbox
echo "</select></td></tr>";
// create the city listbox for no selection
echo "\n<tr><td valign=\"center\" align=\"center\"><font color=\"#000000\" face=\"verdana,arial,helvetica\" size=\"2\">City</font></td><td><select name=\"city\" size=\"1\">";
echo "<option>[no city selected]</option>";
echo "</select></td></tr>";
echo "<tr><td align=\"center\" colspan=\"2\"><input type=\"submit\" name=\"submitcity\" value=\"SUBMIT\"></td></tr>";
echo "</table>";
// finish the javascript and write out
$sJavaScript = $sJavaScript."\n}\n}\n";
echo "\n<SCRIPT LANGUAGE=\"JavaScript\">";
echo "\n".$sJavaScript."\n</SCRIPT>\n";
//close the form
echo "</FORM></center>";
if ("SUBMIT" == $submitcity){
echo "<font color=\"#000000\" face=\"verdana,arial,helvetica\" size=\"2\">Your Selected Country index= ".$country."</font><br>";
echo "<font color=\"#000000\" face=\"verdana,arial,helvetica\" size=\"2\">Your Selected City index= ".$city."</font><br>";
}
?>
</body>
</html>
?>
Have fun dude.
John
