Unable to dynamically populate dropdown with states from a country select

Question

I am trying to make a dynamic dropdown when you select country, the state populates. I have all the information stored in two tables. How exactly do I do this? I have no problems doing the initial country list.

But for states, I don't know how to handle javascript since that is client side and PHP is server side so the PHP would be executed before I get the countryID from the dropdown. Since I don't know how to code time travel, and I know I have seen this sort of thing before, how do I accomplish this?

And I rather not download/pull all the data into an array since that is a total waste of memory so I figured a little sql, php, and javascript would work, but I am definitely missing something.

Removed original source as it is now below.

The database schema and information is below, if interested:

mysql> show tables;
+-----------------+
| Tables_in_earth |
+-----------------+
| regions         |
| subregions      |
+-----------------+
2 rows in set (0.01 sec)


mysql> desc regions;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| country       | varchar(45)      | YES  |     | NULL    |                |
| continent     | varchar(45)      | YES  |     | NULL    |                |
| currency_code | varchar(45)      | YES  |     | NULL    |                |
| currency_name | varchar(45)      | YES  |     | NULL    |                |
| phone_prefix  | varchar(45)      | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> desc subregions;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| region_id | int(10) unsigned | YES  | MUL | NULL    |                |
| name      | varchar(45)      | YES  |     | NULL    |                |
| timezone  | varchar(45)      | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select id,country from regions order by country desc limit 15;
+-----+--------------------------------------+
| id  | country                              |
+-----+--------------------------------------+
| 716 | Zimbabwe                             |
| 894 | Zambia                               |
| 887 | Yemen                                |
| 732 | Western Sahara                       |
| 876 | Wallis and Futuna                    |
| 704 | Vietnam                              |
| 862 | Venezuela                            |
| 336 | Vatican                              |
| 548 | Vanuatu                              |
| 860 | Uzbekistan                           |
| 858 | Uruguay                              |
| 581 | United States Minor Outlying Islands |
| 840 | United States                        |
| 826 | United Kingdom                       |
| 784 | United Arab Emirates                 |
+-----+--------------------------------------+
15 rows in set (0.00 sec)

mysql> select id, name from subregions where region_id=840 limit 5;
+------+------------+
| id   | name       |
+------+------------+
| 3680 | Alaska     |
| 3681 | Alabama    |
| 3682 | Arkansas   |
| 3683 | Arizona    |
| 3684 | California |
+------+------------+
5 rows in set (0.52 sec)

A lot of snipped code, but this is the full(ish) src for index.php. generate.php takes the values and does things with the data.

<!-- snip -->
<script language="javascript" type="text/javascript">
function show(id)
{
   if ( id == "v7" )
   {
      document.getElementById('v8').style.visibility = 'hidden';
      document.getElementById('v7').style.visibility = 'visible';
   }
   else
   {
      document.getElementById('v7').style.visibility = 'hidden';
      document.getElementById('v8').style.visibility = 'visible';
   }

}

function enable()
{
    var value = document.getElementById("country").selectedIndex;
    if ( value > 0 )
    {
       document.getElementById("state").disabled = false;
    }
    else
       document.getElementById("state").disabled = true;
}

function getCountry()
{
   var e = document.getElementById("country");
   var countryID = e.options[e.selectedIndex].value;
   var country = e.options[e.selectedIndex].text;
//   alert( "Selected Country: " + country + "(" + countryID + ") ");
   return countryID;
}

function getStates()
{
    $('#state').html('');
    var e = document.getElementById("country");
    var countryID = e.options[e.selectedIndex].value;
    $.ajax({
            type: "POST",
            url: "index1.php",
            data: {countryID:countryID},
            dataType:'json',
            success: function(result){
                var toAppend = '';
                $.each(data,function(i,o){
                    toAppend += '<option>'+o.id+'</option>';
                });

                $('#state').append(toAppend);       
            },  
    });
}
</script>
</head>
<body>
<!-- major league snip -->
<div class="formDiv">
<form id="frmSigGen" name="frmSigGen" method="post" action="generate.php" class="form">
  <table border="0" align="center" class="SmTable">
      <tr>
      <td class="td"><div align="right">State/Provence:</div></td>
      <td class="td"><label>
        <select name="state" class="dropdown" id="state" disabled="disabled" onchange="getStates();">
        <option value="">State/Provence</option>
   <?php
      $countryID=$_POST['countryID'];
      $query = "select id, name from subregions where region_id=".$countryID;
      $reuslt=RunQuery($query);
      echo json_encode($reuslt);
   ?>
        </select>
      </label></td>
      </tr>

      <tr>
        <td class="td"><div align="right">Country:</div></td>
        <td class="td"><label>
          <select name="country" class="dropdown" id="country" onchange="enable();">
          <option value="">Select Country</option>
          <?php 
             $con = mysql_connect('localhost', 'root', 'password'); 
             if (!$con) 
             {
                die('Could not connect: ' . mysql_error());
             }

             mysql_select_db('earth');
             $query = "select id,country from regions order by country";
             $result = mysql_query($query);

//           while ($row=mysql_fetch_array($result)) 
         while ( $row = mysql_fetch_object( $result ) )              
         { 
          ?>
             <option value=<?php echo $row->id; ?>><?php echo $row->country;?></option>
          <?php } 
              mysql_free_result($result);
          ?>
          </select>

Answer 1

you must use ajax

like this

function getStates()
{
    $('#state').html('');
    var e = document.getElementById("country");
    var countryID = e.options[e.selectedIndex].value;
    $.ajax({
            type: "POST",
            url: "getStates.php",
            data: {countryID:countryID},
            dataType:'json',
            success: function(result){
                var toAppend = '';
                $.each(data,function(i,o){
                    toAppend += '<option>'+o.id+'</option>';
                });

                $('#state').append(toAppend);       
            },  
    });
}

getStates PHP

   $countryID=$_POST['countryID'];
   $query = "select id, name from subregions where region_id=".$countryID;
   $reuslt=RunQuery($query);
   echo json_encode($reuslt);