form auto complete how to restrict results by previous drop down

Question

Hi I have a select field on a form which contains countries. The next field is a text box which is driven by an autocomplete function. The autocomplete function returns cities from a database table.

I want to restrict the cities that the autocomplete will return based on the country value selected in the dropdown. How do I do this?

if it helps here is my form

 $country_list = _get_country_list();
    $form['country'] = array(
            '#type'=>'select',
            '#title'=>'Select Country',
            '#description'=>'Select the country where you want to collect the car',
            '#options'=>$country_list,
    );
$form['officename'] = array(
    '#type'=>'textfield',
    '#title'=> t('Location'),
    '#default_value'=>$form_state['officename'],
    '#size'=>20,
    '#maxlength' => 50,
    '#description' => t('Select the country where you want to pick up the car'),
    '#attributes' => null,
    '#required' => TRUE,
            '#autocomplete_path'=>'location-autocomplete'
);


$form['submit'] = array(
'#type'=>'submit',
'#value'=>'Search Now',
'#submit'=>array('HolidayAutosXML_carsearch_form_submit'),
);

and here is my autocomplete function. I want to restrict the sql with a where country = {country selected in Drop Down List}

function location_autocomplete($string){
$matches = array();
$result = db_query("select officename from {holidayautos_country_list} where officename like :keyword limit 0, 10", array(':keyword'=>"%" . db_like($string) . "%"));
 foreach($result as $row) {
    $matches[$row->officename] = check_plain($row->officename);
  }
drupal_json_output($matches);
}

Many Thanks Simon

Answer 1

Using the links suggested from Anil Sagar, I put together this solution using Ajax.

function HolidayAutosXML_carsearch_form($node, &$form_state) {
  $country_list = _get_country_list();
  $form['country'] = array(
    '#type' => 'select',
    '#title' => 'Select Country',
    '#description' => 'Select the country where you want to collect the car',
    '#options' => $country_list,
    '#ajax' => array(
      'callback' => 'update_officename_autocomplete',
      'wrapper' => 'haofficename',
    ),
  );

  $form['officename'] = array(
'#type' => 'textfield',
'#title' => t('Location'),
'#default_value' => $form_state['office name'],
'#size' => 20,
'#maxlength' => 50,
'#description' => t('Select the location where you want to pick up the car'),
'#attributes' => NULL,
'#required' => TRUE,
    '#autocomplete_path' => 'location-autocomplete',
    '#prefix' => '<div id="haofficename">',
    '#suffix' => '</div>',
  );

  if (!empty($form_state['values']['country'])) {
    $form['officename']['#autocomplete_path'] = "location-autocomplete/{$form_state['values']['country']}";
  }

  $form['submit'] = array(
    '#type'=>'submit',
    '#value'=>'Search Now',
    '#submit'=>array('HolidayAutosXML_carsearch_form_submit'),
  );

  return $form;
}

1. I added an Ajax element to the country drop down, telling it to look for a <div> with an ID equal to "haofficename" and calling a function named update_officename_autocomplete().

2. I used a prefix and suffix element in the office name element to wrap it in a <div> element with the ID haofficename, which Ajax is going to look for.

3. update_officename_autocomplete() simply returns the altered office name element of the form. if you look inside the if statement you will see that it is adding to the #autocomplete_path property. So I had to add a second parameter to my autocomplete function. This function now takes $country and $officename.

   function location_autocomplete($country,$string){ $matches = array(); $result = db_query("select officename from {holidayautos_country_list} where officename like :keyword and country = :country limit 0, 10", array(':keyword'=>"%" . db_like($string) . "%",':country'=>$country)); foreach($result as $row) { $matches[$row->officename] = check_plain($row->officename); } drupal_json_output($matches); }

It works like a charm.