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>");
}
}
}
