Is there a proper way to create a dynamic countries/states/cities dropdown so it doesn't slow down?

[imgrooot]

This is the code for a dynamic dropdown list which i use to create a record. It works. But i find it slow when updating a record. It takes time to load the cities dropdown data. 

 

I am wondering, is there a more efficient way to do this dropdown so it loads the data more quickly? 

<script>
  function getCity(val) {
    $.ajax({
    type: "POST",
    url: "../snippets/get_cities.php",
    data:'state_id='+val,
    success: function(data){
      $("#city-list").html(data);
    }
    });
  }
  function getState(val) {
    $.ajax({
    type: "POST",
    url: "../snippets/get_states.php",
    data:'country_id='+val,
    success: function(data){
      $("#state-list").html(data);
    }
    });
  }
  function selectCountry(val) {
    $("#search-box").val(val);
    $("#suggesstion-box").hide();
  }
</script>
<fieldset>
  <label>Location:</label><br/>
  <select name="country" id="country-list" onChange="getState(this.value);">
    <option value="">Select Country</option>
    <?php
    $get_countries = $db->prepare("SELECT * FROM countries2");
    $get_countries->execute();
    $result_countries = $get_countries->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_countries) > 0) {
      foreach($result_countries as $row) {
        $get_country_id     = 	trim($row['id']);
        $get_country_name   = 	trim($row['name']);
        ?><option value="<?php echo $get_country_id; ?>" <?php if($record_country_id == $get_country_id) { echo 'selected'; }; ?>><?php echo $get_country_name; ?></option><?php
      }
    }
    ?>
  </select>
  <select name="state" id="state-list" onChange="getCity(this.value);">
    <option value="">Select State/Province</option>
    <?php
    $get_states = $db->prepare("SELECT * FROM states");
    $get_states->execute();
    $result_states = $get_states->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_states) > 0) {
      foreach($result_states as $row) {
        $get_state_id     = 	trim($row['id']);
        $get_state_name   = 	trim($row['name']);
        ?><option value="<?php echo $get_state_id; ?>" <?php if($record_state_id == $get_state_id) { echo 'selected'; }; ?>><?php echo $get_state_name; ?></option><?php
      }
    }
    ?>
  </select>
  <select name="city" id="city-list">
    <option value="">Select City</option>
    <?php
    $get_cities = $db->prepare("SELECT * FROM cities");
    $get_cities->execute();
    $result_cities = $get_cities->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_cities) > 0) {
      foreach($result_cities as $row) {
        $get_city_id     = 	trim($row['id']);
        $get_city_name   = 	trim($row['name']);
        ?><option value="<?php echo $get_city_id; ?>" <?php if($record_city_id == $get_city_id) { echo 'selected'; }; ?>><?php echo $get_city_name; ?></option><?php
      }
    }
    ?>
  </select>
</fieldset>

[ginerjm]

Do it once at the start of a session and save it in a session var. Then check for the existence of the var at the start of your build process and if it is not set, re-build it.

[gizmola]

The first thing that is obvious from your code is that you apparently have no relationship between country, city and state.  This leads to the obvious issue that if a user selects a country where states are not relevant, or selects a country and state, you still will get every city in the world that is loaded.  

 

Doing a well organized world model is non-trivial.  I did one in the past for a system that tracked international rights across territories worldwide, and it was complicated and involved a good deal of research, maintenance and user upkeep.  

 

Your code seems to be missing that, and I'm not sure what the use case is, but it seems that you should have some intelligence built into it, that segments your results based on whatever choices have been made previously.  So for example, if the logic is that you only choose a state  if your country is 'US', then that needs to be coded into your frontend.  

 

If you've chosen 'US' and the state of 'CA' then the cities should be constrained to only those within 'CA'.

 

This is just basic database design, and with your current approach the user could pick nonsensical combinations ('Iraq', State: 'ND', City: 'Boston').

 

ginerjm provided a suggestion that is beneficial if these tables are reused multiple times, but it will not speed up a single access at all.  

 

This is a static list that will rarely change, so it is safe to cache it in a shared list for long periods of time.  In general memory caching cures many ills, so rather than storing it in a session, I would suggest memcache or redis.  

 

This assumes that you are able to allocate memory for, install and configure one of those solutions, but they are invaluable for buffering your database.  

 

The other possible improvement is to convert your code to provide "Autocomplete" incremental search, where the user types in a few characters of the country name and you return the results and refines based on additional character entry.  Here's the documentation for the jQuery UI widget for 'Autocomplete'.  If you want to leverage this, focus in on the documentation for the 'source' option.