How to send values to an external database with a custom module? Working on Drupal 7.54

I am more a site builder than a developer but now I have no choice, I need to write a little module for Drupal 7.

So, I was wondering if someone could help me to write this custom module (at least help me to start).

Here is my problem :

I need to send form values to an external database.

For the moment, I managed to tell Drupal that it will need another database (in settings.php, everything is fine here).

Following "Inserting data to external DataBase
", I managed to create a little form to add a new line in the external database (using hook_form, hook_form_submit, db_set_active and db_insert).

Now I would like to complete this custom module to achieve this :


an user open the node edit form
an user submits changes
the form is submitted
the form values are saved as usual on Drupal database
AND the same values are saved on the external database


Finally, I need a module to synchronize Drupal nodes with external database tables.

I suppose this is something totally doable and easy for someone used to Drupal API.
Thanks in advance for any help on this.

PS : you may find a first brainstorming on this problem here : How to edit external database from Drupal 7 interface?

Related questions

How to define and use an external database connection in a custom module
I'm a little fuzzy on the details of what you already have but the code below should help you get you in the right direction (I think). It will check for changes to the fields, make sure the node exists in the external database and then update the external database table.

Just remember to change the db_set_active statements to the name of your database and change the table names.

As a side note, as you said you are more of a site developer, if you install the Devel module you can use dpm() to print variables, arrays,  messages etc. to the Drupal message space. E.g. if you wanted to see what was in $new, after the foreach() where we add the updated fields to $new, adding a new line with just dpm($new) will display the array in a structured and pretty way.

Its an invaluable tool for module development!

/**
 * Implements hook_node_update().
 */
function external_update_node_update($node) {
  // Get a list of fields for the content type.
  $fields = field_info_instances("node", $node-&gt;type);

  // Set variables as arrays.
  $old = array();
  $new = array();

  // Get the values of the fields for both the original and updated states
  // and add them to the arrays.
  foreach ($fields as $key =&gt; $values) {
    $temp_field = field_get_items('node', $node-&gt;original, $key);
    if (isset($temp_field[0])) {
      $old[$key] = $temp_field[0]['value'];
    }
    else {
      $old[$key] = NULL;
    }
  }
  foreach ($fields as $key =&gt; $values) {
    $temp_field = field_get_items('node', $node, $key);
    // If the field is not empty...
    if (isset($temp_field[0])) {
      $new[$key] = $temp_field[0]['value'];
    }
    // Else add an empty value.
    else {
      $new[$key] = NULL;
    }
  }

  // Check if there are any differences between the new and original states.
  $diff = array_diff($new, $old);

  // If there are differences then an update can occur.
  if (!empty($diff)) {
    // Start the database transaction in case something goes wrong.
    $transaction = db_transaction();

    // Add a try/catch to be able to roll back the database if it errors out.
    try {
      /** Check all node fields exists in external database. **/

      // Get database fields.
      $sql = "SHOW COLUMNS FROM update_save";
      // Switch to external database.
      db_set_active('external');
      $query = db_query($sql);
      // Switch back to drupal db.
      db_set_active();

      // Add list of database fields to array for use later.
      $db_fields = array();
      foreach ($query as $row) {
        $db_fields[$row-&gt;Field] = $row-&gt;Field;
      }

      // Check if the node contains new fields that are not in the database.
      $field_diff = array_diff_key($fields, $db_fields);
      // If extra fields exits set a log message
      // and remove these fields from the update.
      if (!empty($field_diff)) {
        foreach ($field_diff as $missing_field) {
          watchdog('node_update', "The field $missing_field is missing from the external database.", WATCHDOG_ALERT);
          unset($new[$missing_field]);
        }
      }
    }
    // If something went wrong and it threw an error,
    // catch the exception, roll back any database changes and log the error.
    catch (Exception $e) {
      $transaction-&gt;rollback();
      watchdog('node_update', "&lt;pre&gt;$e&lt;/pre&gt;");
    }

    try {
      // Check if node already exists in the external database.
      $sql = "SELECT nid FROM update_save WHERE nid = :nid";
      // Switch to external database.
      db_set_active('external');
      $query = db_query($sql, array(":nid" =&gt; $node-&gt;nid));
      db_set_active();
      $nid = "";
      foreach ($query as $row) {
        // If there are any results add it to the $nid variable.
        $nid = $row;
      }

      // If the query came back with results and therefore $nid is not empty,
      // then the update can continue.
      if (!empty($nid)) {
        // Switch to external database.
        db_set_active('external');
        db_update('update_save')
          -&gt;fields($new)
          -&gt;condition('nid', $node-&gt;nid)
          -&gt;execute();
        db_set_active();
      }
      // Else add a watchdog log.
      else {
        watchdog('node_update', "The node currently doesn't exist in the external database.", WATCHDOG_ALERT);
      }
    }
    // If something went wrong and it threw an error,
    // catch the exception, roll back any database changes and log the error.
    catch (Exception $e) {
      $transaction-&gt;rollback();
      watchdog('node_update', "&lt;pre&gt;$e&lt;/pre&gt;");
    }
  }
}