Dynamic drop down list with AJAX and database

Question

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.

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.

Code as follows

dropDown.jsp:

<script>
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 && 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);
}

</script>

<FORM NAME="myform">
<SELECT NAME="state" onChange="getCityDropdown()">
    <OPTION VALUE="1">Georgia</option>
    <OPTION VALUE="2">Washington</option>
</SELECT>
</FORM>
<div id="second_dropdown_code"></div>

getCities.java

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") + "<br/>" + resultSet.getString("city") + "<br/>" + resultSet.getString("INSTRUCTOR") + "<br/>" + resultSet.getString("BUILDING") + "<br/>" + resultSet.getString("ROOM") + "<br/>");
            //out.println(resultSet.getInt("cityid") + "<br/>" + resultSet.getString("city") + "<br/>" );
            //out.println("<select name=\"city\"><option value=\"1\">Atlanta</option><option value=\"2\">Walla Walla</option></select> ");
        }

        resultSet.close();
        preparedStatement.close();
        connection.close();

    } catch (SQLException ex) {
        Logger.getLogger(getCities.class.getName()).log(Level.SEVERE, null, ex);
    }
}

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.

Any guidance would be apperciated, thanks.

Answer 1

There is a fundamental problem in the design: there is no way, with your current database design, to know which cities belong to which state.

The tables should look like that:

State
    - id
    - name

City
    - id
    - name
    - state_id (foreign key to State.id)

And the SQL query to get the cities of a given state ID would thus simply look like this:

select city.id, city.name from city
where city.state_id = ?
order by city.name