0

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

  • It sounds like you want to basically run the same queries twice for two separate connections? Save yourself the hours of pain trying to convince Drupal to do that, and just setup replication between the two databases – Clive Mar 20 '17 at 18:37
  • My problem is that the two databases have a different structure. On the one hand I have the Drupal one with tables for each field, nid references, etc. and on the other hand I have a standard base (created first on Access) with "nodes as lines", "fields as columns" and "content types as tables" (sort of). Anyway, thanks for this option, I will study it (even if I would need to learn this "replication" process). – Toki Mar 21 '17 at 8:24
  • It's not that tricky actually, it's mostly config and the engine itself takes care of the grunt work. But if the table structures are different that might be a difficult option to use – Clive Mar 21 '17 at 8:28
  • Is there a solution with Rules for example? I mean, for example, each time a node is created or updated, use a PHP script action with db_set_active, db_insert, db_update and use tokens as values to transfer? – Toki Mar 21 '17 at 11:09
1

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->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 => $values) {
    $temp_field = field_get_items('node', $node->original, $key);
    if (isset($temp_field[0])) {
      $old[$key] = $temp_field[0]['value'];
    }
    else {
      $old[$key] = NULL;
    }
  }
  foreach ($fields as $key => $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->Field] = $row->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->rollback();
      watchdog('node_update', "<pre>$e</pre>");
    }

    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" => $node->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')
          ->fields($new)
          ->condition('nid', $node->nid)
          ->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->rollback();
      watchdog('node_update', "<pre>$e</pre>");
    }
  }
}
  • Also, don't forget to change the function name to the name of your custom module!!!! – Jimmyb_1991 Mar 22 '17 at 14:26
  • Thank you so much for this highly detailed code. My only concern is that the external database has a different structure, I do not need a clone of the node but a mapping between Drupal fields and table columns. Anyway, your code will be a solid work basis. Merci! (I may have found a solution using Rules and custom PHP with tokens but still a custom module is always useful). – Toki Mar 22 '17 at 14:54
  • Ah, using Rules might be slightly easier. However, you could adapt this code to map the updated values to different columns and or tables. Instead of just passing the $new variable to ->fields you could do: ->fields(array( 'name external db field' => $new->field_you_want_to_map, )) You could repeat this if all fields are in the same table, or run multiple db_update statements. – Jimmyb_1991 Mar 22 '17 at 15:15
  • 1
    If you get it working using Rules and custom php, would you mind posting here so it can hopefully help others too :) – Jimmyb_1991 Mar 22 '17 at 15:16
  • Of course, I will have to test this option and as soon as I have something solid I will post here. Thank you again Jimmy! Keep in touch ^^ – Toki Mar 23 '17 at 10:09
0

I posted my answer here to keep all elements together so future users can read everything at the same place. Thanks Jimmyb_1991 for the custom module. I will probably use it later to improve my own solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.