mbharris.co.uk
Mike Harris is a Software Engineer and Agile/Lean project manager, coach, consultant and trainer based in Oxford, UK

Example of field auto-complete and form auto-populate and using a foreign database in Drupal 7

Fri, 08/08/2014

I was recently needing to make an improvement to a development skateboard that I'd done for some users a few months prior. The users had had a requirement for a new system to be delivered in a time frame that meant implementing it in Drupal seemed like the right thing to do.

The application worked and the users were able to get on with their jobs.

However, over time it emerged that they were loosing time having to rekey information from one of our existing systems (written in Perl with MySQL as it's back-end) into the Drupal application I had made for them.

I realised that what I needed to do was to be able to fetch existing records out of the existing system (source) and populate a form they were using to add content (a standard add node form) into the Drupal application (destination).

This meant that my Drupal app needed to acquire two abilities:

I implemented it in Drupal by creating my own custom Drupal module and using the following components:

Fetching the list of references for the add node form autocomplete field

When I originally implemented the Drupal app I'd used the default title field to be used as the unique record reference for each node. It's therefore this field that I had to alter using Drupal's hook_form_<form_id>_alter() hook. In the following, all I do is at a value for the #autocomplete_path attibute of the form's title field directly by manipulating the Drupal's form hash:

//implements hook_form__alter 
function example_form_mycontenttype_node_form_alter(&$form, &$form_state, $form_id) { 

  $form['title']['#autocomplete_path'] = 'records/listreferences'; 

}

If one reloads the node form, one'll see that there's now one of those little spinner things to the far right of the title field:

So this calls a path below my Drupal 'root' directory (in this case records/listreferences) to fetch the data to provide the list of values for the form auto-complete. Drupal is expecting the data back in the form of a JSON hash.

Here's the function I use the fetch the references from database based on the variable $searchString that contains the characters I've so far typed into the title field:

// custom function 
function example_fetch_references_list($searchString = "") { 
  $matches = array(); 
  if ($searchString) { 
    db_select('foreign_database'); 
    $result = db_query("SELECT reference FROM records WHERE reference LIKE :searchString", 
      array(':searchstring' => $searchString . "%")
    ); 
    foreach ($result as $record) { 
      $matches[$record->reference] = check_plain($record->reference); 
    } 
    db_select('default'); 
  } 

  drupal_json_output($matches); 

}

You see it calls drupal_json_output() to return a list of references as a JSON hash. Now I need to make sure that when I visit http://mydrupalsite.com/records/listreferences, that it knows to call the function example_fetch_references_list() and present me with some JSON. I do this by setting up a special type of menu item in Drupal 7 that is known as a callback. This does some action when a given URL is visited (apparently in Drupal 8 this will have it's own type of Entity rather than be a hack of the menu system):

// implements hook_menu() 
function example_menu() { 
  $items['records/listreferences'] = array( 
    'title' => 'Fetch a list of references in a JSON hash', 
    'page callback' => 'example_fetch_references_list', 
    'access arguments' => array('foreign database form autocomplete'), 
    'type' => MENU_CALLBACK, 
  ); 

  return $items; 
} 

Notice there the parameter to 'access arguments' ? This allows me to set permission on the URL records/listeferences to ensure that only certain user roles are allowed to query my database. Drupal's quite clever with this too: If a user does not have the permissions to access this list then the field isn't auto-complete enabled. To set this, I add my own permission to the system using hook_permission():

// Implements hook_permission()
function example_permission() {
  return array(
    'foreign database form autocomplete' => array(
       'title' => t('Enable the foreign database form autocomplete function'),
       'description' => t('Use a foreign db to fetch an list of references into the title field and then autopopulates a form.'),
     ),
  );
} 

Now, when I go into the Drupal 7 Administrative Menu People and then Roles, I see my permission listed and therefore grant my user access to it. Note you will need to do this in this example to make it work, or ditch using access arguments at all, but this isn't recommended as you're then opening your database up to the world.

Acting on the form field and then auto-populate the form

So the next part is to act on what's been chosen in the form auto-complete above and then auto-populate the form. I used an external JavaScript file to provide a call-back on the Title field's onblur event to then make an AJAX request to fetch the records details.

Firstly though let's look at the function that fetches the record:

// custom function 
function example_fetch_record_details_from_reference($recordReference = "") { 

  $response = array(); 
  if ($recordReference != '') { 
    db_select('foreign_database'); 
    $result = db_query("SELECT forename, surname, mobile, irc_channel FROM records WHERE reference = :reference", 
      array(':reference' => $recordReference)); 
    if ($result->rowCount() > 0) { 
      $response = $result->fetchAssoc(); 
    } else { 
      $response['message'] = "The parameter of " . $recordReference . " you provided me with did not return any data!"; 
    } 
    db_select('default'); 
  } else { 
    $response['message'] = "You need to provide me with a parameter!"; 
  } 
  echo drupal_json_encode($response); 
}

Simple eh? Well hopefully it'll make sense to you. The function handles three states, one returning valid data and two error messages:

In all instances the data is returned in the form of a JSON hash using drupal_json_encode(); in the case of the data it converts straight from a PHP associative array returned by Drupal's fetchAssoc() method right into JSON.

Again we set up a menu callback to call our function and protect our data using the same permissions as we used previously:

// implements hook_menu() 
function example_menu() { 
  $items['records/listreferences'] = array( 
    'title' => 'Fetch a list of references in a JSON hash', 
    'page callback' => 'example_fetch_references_list', 
    'access arguments' => array('foreign database form autocomplete'), 
    'type' => MENU_CALLBACK, 
  ); 

  $items['records/view'] = array( 
    'title' => 'Given a reference fetch record in a JSON hash', 
     'page callback' => 'example_fetch_record_details_from_reference', 
     'access arguments' => array('foreign database form autocomplete'), 
     'type' => MENU_CALLBACK, 
  ); 

  return $items; 
} 

Here's our JavaScript file that calls our Drupal 7 callback URL and fetches the JSON hash and then populates the appropriate form fields:

//file: form-autocomplete.js
jQuery(document).ready(function() {
    // I had to use onblur rather than onchange for this to work correctly
    jQuery("#edit-title").blur(function() {
        jQuery.ajax({
            type: "POST",
            url: "/records/view/" + jQuery("#edit-title").val(),
            dataType: 'json',
            success: function(response) {
                // a successful record match; now populate the form fields
                if (response.surname != '') {
                    jQuery('#edit-field-forename-und-0-value').val(response.forename);
                    jQuery('#edit-field-surname-und-0-value').val(response.surname);
                    jQuery('#edit-field-mobile-und-0-value').val(response.mobile);
                    jQuery('#edit-field-irc_channel-und option').filter(function() {
                        return jQuery(this).text() == response.irc_channel;
                    }).attr('selected', true);
                }
            },
            error: function(response) {
                // an error; silently log to the console in JavaScript
                console.log(response.message);
                return false;
            },
        })
    })
});

Last thing to do is to hook the JavaScript file up to the form and for this I've used Drupal 7's drupal_add_js() function to load the JavaScript file only upon loading this form:

//implements hook_form__alter 
function example_form_mycontenttype_node_form_alter(&$form, &$form_state, $form_id) { 

  $form['title']['#autocomplete_path'] = 'records/listreferences'; 
  drupal_add_js(drupal_get_path('module', 'example') .'/form-automcomplete.js'); 

}

And that's it! It works a treat. I'm sure, as always with Drupal, there's other ways of doing this, possibly more efficient, but this seemed like a fairly good approach to me.

(c) 2017 Mike Harris & Broad Bean Productions Ltd. Copyleft where specified.