title: Dynamic drop down list with AJAX and database
body: <p>I'm hoping someone could guide me in the right direction. What I'm trying to do is create two drop down lists where the second one (cities) depends on the first one (states). For example, when I click on Washington I want the second drop down menu to list Seattle and Walla Wallal; Georgia, Atlanta, Athens, etc. Ultimately my goal is the link several of the lists together (country -> state -> county -> city) but if I can get these two working I think I could figure out how to do that. </p>
  
  <p>So far I have two databases set up. Table CITY with cityid (primary) and cityname as columns and table STATE with stateid (primary) and statename as columns. </p>
  
  <p>Code as follows</p>
  
  <p>dropDown.jsp:</p>
  
  <pre><code>&lt;script&gt;
  function createRequestObject(){
      var req;
      if(window.XMLHttpRequest){
          //For Firefox, Safari, Opera
          req = new XMLHttpRequest();
      } else if(window.ActiveXObject){
          //For IE 5+
          req = new ActiveXObject("Microsoft.XMLHTTP");
      } else {
          //Error for an old browser
          alert('Your browser is not IE 5 or higher, or Firefox or Safari or Opera');
      }
      return req;
  }
  
  //Make the XMLHttpRequest Object
  var http = createRequestObject();
  function sendRequest(method, url){
      if(method == 'get' || method == 'GET'){
          http.open(method,url);
          http.onreadystatechange = handleResponse;
          http.send(null);
      }
  }//end function send
  
  function handleResponse(){
      if(http.readyState == 4 &amp;&amp; http.status == 200){
          var response = http.responseText;
          if(response){
              document.getElementById("second_dropdown_code").innerHTML = response;
          }
      }
  }
  
  function getCityDropdown() {
      var w = document.myform.mylist.selectedIndex;
      var country_id = document.myform.mylist.options[w].value;
      sendRequest('GET','getCites.do?stateid=' + state_id);
  }
  
  &lt;/script&gt;
  
  &lt;FORM NAME="myform"&gt;
  &lt;SELECT NAME="state" onChange="getCityDropdown()"&gt;
      &lt;OPTION VALUE="1"&gt;Georgia&lt;/option&gt;
      &lt;OPTION VALUE="2"&gt;Washington&lt;/option&gt;
  &lt;/SELECT&gt;
  &lt;/FORM&gt;
  &lt;div id="second_dropdown_code"&gt;&lt;/div&gt;
  </code></pre>
  
  <p>getCities.java</p>
  
  <pre><code>protected void doGet(HttpServletRequest request, HttpServletResponse response)
          throws ServletException, IOException {
      PrintWriter out = response.getWriter();
      try {
          processRequest(request, response);
  
          String sql = "select * from CITY";
          Connection connection = datasource.getConnection();
          PreparedStatement preparedStatement = connection.prepareStatement(sql);
          ResultSet resultSet = preparedStatement.executeQuery();
  
          while (resultSet.next() ) {
              //out.println(resultSet.getInt("cityid") + "&lt;br/&gt;" + resultSet.getString("city") + "&lt;br/&gt;" + resultSet.getString("INSTRUCTOR") + "&lt;br/&gt;" + resultSet.getString("BUILDING") + "&lt;br/&gt;" + resultSet.getString("ROOM") + "&lt;br/&gt;");
              //out.println(resultSet.getInt("cityid") + "&lt;br/&gt;" + resultSet.getString("city") + "&lt;br/&gt;" );
              //out.println("&lt;select name=\"city\"&gt;&lt;option value=\"1\"&gt;Atlanta&lt;/option&gt;&lt;option value=\"2\"&gt;Walla Walla&lt;/option&gt;&lt;/select&gt; ");
          }
  
          resultSet.close();
          preparedStatement.close();
          connection.close();
  
      } catch (SQLException ex) {
          Logger.getLogger(getCities.class.getName()).log(Level.SEVERE, null, ex);
      }
  }
  </code></pre>
  
  <p>I know I need some sort of if statement in the while loop but I'm not sure where I'm getting the parameter from to compare the stateid with the cityid.</p>
  
  <p>Any guidance would be apperciated, thanks.</p>