Automatically fill in “City” field when “Postal code” is entered

Question

I encountered a problem that has been discussed many times but for which no solution has been written down yet. This is about Drupal 7.

On my node add form, I have an "Address" field; a field collection item that consists of a "Postal code" field, a "City" field, and some other fields. All addresses will be German addresses and in Germany a 5-digit postal code uniquely determines a city, i.e. no two cities have the same postal code. I would like to manipulate the node add form such that when a postal code is entered, the city field is automatically filled in.

http://www.zippopotam.us offers an API that lets me achieve this as follows. (Note that on the node add form the postal code field's id is edit-field-address-und-0-field-postal-code-und-0-value and the city field's id is edit-field-address-und-0-field-city-und-0-value.)

1) I have added a custom module that contains the following code.

function custom_form_alter(&$form, &$form_state, $form_id) {
  $form['#after_build'][] = 'custom_afterbuildfunction';
}

function custom_afterbuildfunction($form_element, &$form_state) {
  drupal_add_js('http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js', 'external');
  drupal_add_js('sites/all/modules/custom/custom.js', 'file');

  return $form_element;
}

2) And then, in the module's directory, I add the custom.js file which contains the following code.

$(function() {  
  // Disable City and State on Load
  $(document).ready( function() {           
    $('#edit-field-address-und-0-field-city-und-0-value').attr('readonly','readonly');
  });

  // OnKeyDown Function
  $("#edit-field-address-und-0-field-postal-code-und-0-value").keyup(function() {
    var zip_in = $(this);
    var zip_box = $('#field-address-und-0-field-postal-code-add-more-wrapper');

    if (zip_in.val().length<5) {
      zip_box.removeClass('error success');
    }
    else if (zip_in.val().length>5) {
      zip_box.addClass('error').removeClass('success');
    }
else if ((zip_in.val().length == 5)) {      
      // Make HTTP Request
      $.ajax({
        url: "http://api.zippopotam.us/de/" + zip_in.val(),
        cache: false,
        dataType: "json",
        type: "GET",
        data: "de/" + zip_in.val(),
        success: function(result, success) {
          // German Post Code Records Officially Map to only 1 Primary Location
          places = result['places'][0];
          $("#edit-field-address-und-0-field-city-und-0-value").val(places['place name']);
          zip_box.addClass('success').removeClass('error');
        },
        error: function(result, success) {
          zip_box.removeClass('success').addClass('error');
        }
      });
    }
  });
});

However, I would really like to independent of Zippopotamus. I have imported a table of all postal codes and corresponding cities into my database, called "zipcodes". It has a column called "zip" and a column called "city". This table comes with Location module. I would really like to get the city corresponding to the postal code from my own database via db_query("select city from {zipcodes} where zip = :zip limit 1", array(":zip" => '$zip_in'))->fetchField(); and then fill it in to the "City" field.

*** Edit ***

I tried the following modified code, which unfortunately does not work.

1) In the custom module:

/**
 * Implementation of hook_menu()
 */

function product_menu() {
$items = array();

$items['get_city'] = array(
        'page callback' => 'custom_get_city',
        'access arguments' => TRUE,
        'type' => MENU_CALLBACK,
);

return $items;
}

/**
 * Implementation of hook_form_alter()
 */

function custom_form_alter(&$form, &$form_state, $form_id) {

if ($form_id == 'job_offer_node_form') {

    $form['#after_build'][] = 'custom_validate_and_populate';

}
}

/**
 * Implementation of #after_build custom function hook_validate_and_populate()
 */

function custom_validate_and_populate($form_element, &$form_state) {

drupal_add_js('sites/all/modules/custom/custom.js', 'file');
return $form_element;

}

/**
 * Implementation of hook_get_city()
 * This function ONLY introduces a new variable that carries the city name corresponding to the entered zip code.
 * replacement of Zippopotamus API;
 * does NOT yet populate any fields; this happens in the corresponding javascript file
 */

function custom_get_city() {
$zip = strtolower($_GET['zip_in']);
$city = db_query("select city from {zipcodes} where zip = :zip limit 1", array(":zip" => $zip))->fetchField();

}

2) In the javascript file:

$(function() {

        // Set city to 'readonly' on load.
        $(document).ready( function() {

            $('#edit-field-address-und-0-field-city-und-0-value').attr('readonly','readonly');

        });

        // keyup function
        $("#edit-field-address-und-0-field-postal-code-und-0-value").keyup(function() {
            var zip_in = $(this);
            var zip_box = $('#field-address-und-0-field-postal-code-add-more-wrapper');

            if ((zip_in.val().length == 5) ) 
            {

                // Make HTTP Request
                $.ajax({
                    url: "/get_city",
                    cache: false,
                    dataType: "json",
                    type: "GET",
                    data: "zip_in=" + zip_in.val(),
                    success: function(result, success) {
                        $("#edit-field-address-und-0-field-city-und-0-value").val('$city');
                        zip_box.addClass('success').removeClass('error');
                    },
                    error: function(result, success) {
                        zip_box.removeClass('success').addClass('error');
                    }
                });

            }
});
});

Answer 1

Perhaps something as follows? i did not try the code below it is based on something I am working on (see : http://drupal.org/node/1956690 ) .. I am trying to autofill options of a 'selectbox'. Which is working .. And since you are great with javascript :) How do I prevent prevent the 'enter key on a form' ? :) (see : http://drupal.org/node/370694 )

(edit: I tested the code below and it works. Type in the postalcode in the searchbox, hit tab-key and the CITY textfield will be filled with data from your dbase :) ). THe only issue that remains is that when the user hits the enter button, the form will be submitted. Hence my question, how do we prevent the enter key invoking the form-submit?

(edit 2: to prevent to submit the form when hitting return-key

$form['#attributes'] = array('onsubmit' => 'return false');

) the above will disable submit all together

scenario 2 (javascript):

  $('#edit-searchfield').keypress(function(event) {
   if (event.keyCode == '13') {
       event.preventDefault();
   }
});

#

<?php


function autofill_menu(){

    $items['test/cities'] = array(
        'title' => 'autofill from db',
        'page callback' => 'drupal_get_form',
        'page arguments' => array('autofill_cities'),
        'access callback' => TRUE,
        'weight' => 4,
        'type' => MENU_NORMAL_ITEM,
        );

    return $items;
    }

function autofill_cities($form, &$form_state) {

    $form['searchcity']['input'] = array(
        '#title' => t('Search database and fill select options'),
        '#type' => 'textfield',
        '#required' => TRUE,
        '#maxlength' => 115,
        '#size' => 55,
        '#weight' => 0,
        '#default_value' => 'look for it',
         "#executes_submit_callback" => FALSE,
        '#ajax' => array(
            'event' => 'change',
            'callback' => '_get_select',
            'wrapper' => 'replace_textfield_div',
            ),
    );


    $form['city'] = array(
        '#type' => 'textfield',
        '#title' => 'City',
        '#size' => 15,
        // The prefix/suffix provide the div that we're replacing, named by
        // #ajax['wrapper'] above.
        '#prefix' => '<div id="replace_textfield_div">',
        '#suffix' => '</div>',
    );


    if (!empty($form_state['values']['input'])) {
        $test =  $form_state['values']['input'];
        // dpm($test);
        $getrow = _get_cities($form_state['values']['input']);  
        $form['city']['#value'] = $getrow;  
        }

  return $form;
}

function _get_select($form, $form_state) {
  // The form has already been submitted and updated. We can return the replaced
  // item as it is. 
  return $form['city'];
}

function _get_cities($key) {

$query = db_select('zipcodes', 'ct')
        ->fields('ct')
        ->condition('zip', array($key)  , '=')
        ->execute()
        ->fetchAssoc();

return($query['Product']);

}