Countries and Regions, Auto Fill

Hi,

How would i go about doing this?

I have a table in a mysql database with all the countries.

Then i have another tables with regions and each region has the id of the country it is associated with stored as countryid.

How would i do menu where when i select a country the next menu fields would auto fill with the relevant regions.

Cheers,
Adam

 

Can anyone help with this?

 

pm me some contact info's I will help you with this for free.

 

Can anyone see anything wrong with this i have done?

HTML Form

<form method="post" action="#">
<p><label>country: <select id="country" name="country">
<option value="United Kingdom">United Kingdom</option>

<option value="184">United States</option>
<option value="185">Australia</option>
<option value="183">Japan</option>
</select></label>
<label>city: <select id="city" name="city">
</select></label></p>
</form>

Code (markup):

PHP

<?php

$country=@$_GET['country'];
$q = mysql_query("SELECT * FROM regions WHERE countryid='$country' ORDER BY region ASC");
while ($r = mysql_fetch_assoc($q)){
echo "|".$r['city'];
}

?>

Code (markup):

JS

var request = false;
/*@cc_on @*/
/*@if (@_jscript_version >= 5)
try {
request = new ActiveXObject("Msxml2.XMLHTTP");
} catch (e) {
try {
request = new ActiveXObject("Microsoft.XMLHTTP");
} catch (e2) {
request = false;
}
}
@end @*/
if (!request && typeof XMLHttpRequest != 'undefined') {
request = new XMLHttpRequest();
}

function fillSelect(country) {
var url = "Update.php?country=" + escape(country);
request.open("GET", url, true);
request.onreadystatechange = go;
request.send(null);
}

function go() {
if (request.readyState == 4) {
if (request.status == 200) {
var response = request.responseText;
var list=document.getElementById("city");
var cities=response.split('|');
for (i=1; i<cities.length; i++) {
   var x=document.createElement('option');
   var y=document.createTextNode(cities[i]);
   x.appendChild(y);
   list.appendChild(x);
   }
  }
 }
}

function initCs() {
var country=document.getElementById('country');
country.onchange=function() {
 if(this.value!="") {
  var list=document.getElementById("city");
  while (list.childNodes[0]) {
list.removeChild(list.childNodes[0])
}
  fillSelect(this.value);
  }
 }
 fillSelect(country.value);
}

Code (markup):

Can anyone see anything wrong with any of this?

Cheers,
Adam

 

php..

<?php
//totally insecure query
$country=@$_GET['country'];
//www.php.net/mysql-real-escape-string for further reading
$q = mysql_query("SELECT * FROM regions WHERE countryid='$country' ORDER BY region ASC");
//html below
?>
<form method="post" action="#">
<p><label>country: <select id="country" name="country">
<?php
//more php
while ($value=mysql_fetch_row($q)) {
echo '<option value="' . $value[0] . '">' . $value[0] . '</option>';
}
//end of the php
?>
</select>
</label>
</form>

PHP:

Hopefully this gives you the general idea - untested as currently typing this and watching a movie in bed on laptop

 

Thank you for this sharqi but i need a solution which is automatic so the page does not have to refresh to load the next results.

 

I think you need onChange event for countries select box e.g.: <select id="country" name="country" onChange="fillSelect(this.selected.value);">

also
use a JS fremework. It will reduce your js development time 100 times.
use JSON notation for ajax method out.