function drupal_write_record

You are here

7 common.inc drupal_write_record($table, &$record, $primary_keys = array())
6 common.inc drupal_write_record($table, &$object, $update = array())
8 schema.inc drupal_write_record($table, &$record, $primary_keys = array())

Save a record to the database based upon the schema.

Default values are filled in for missing items, and 'serial' (auto increment) types are filled in with IDs.

Parameters

$table: The name of the table; this must exist in schema API.

$object: The object to write. This is a reference, as defaults according to the schema may be filled in on the object, as well as ID on the serial type(s). Both array an object types may be passed.

$update: If this is an update, specify the primary keys' field names. It is the caller's responsibility to know if a record for this object already exists in the database. If there is only 1 key, you may pass a simple string.

Return value

Failure to write a record will return FALSE. Otherwise SAVED_NEW or SAVED_UPDATED is returned depending on the operation performed. The $object parameter contains values for any serial fields defined by the $table. For example, $object->nid will be populated after inserting a new node.

Related topics

8 calls to drupal_write_record()
blogapi_metaweblog_new_media_object in modules/blogapi/blogapi.module
Blogging API callback. Inserts a file into Drupal.
contact_admin_edit_submit in modules/contact/contact.admin.inc
Process the contact category edit page form submission.
file_save_upload in includes/file.inc
Saves a file upload to a new location.
node_save in modules/node/node.module
Save a node object into the database.
taxonomy_save_term in modules/taxonomy/taxonomy.module
Helper function for taxonomy_form_term_submit().

... See full list

File

includes/common.inc, line 3515
Common functions that many Drupal modules will need to reference.

Code

function drupal_write_record($table, &$object, $update = array()) {
  // Standardize $update to an array.
  if (is_string($update)) {
    $update = array($update);
  }

  $schema = drupal_get_schema($table);
  if (empty($schema)) {
    return FALSE;
  }

  // Convert to an object if needed.
  if (is_array($object)) {
    $object = (object) $object;
    $array = TRUE;
  }
  else {
    $array = FALSE;
  }

  $fields = $defs = $values = $serials = $placeholders = array();

  // Go through our schema, build SQL, and when inserting, fill in defaults for
  // fields that are not set.
  foreach ($schema['fields'] as $field => $info) {
    // Special case -- skip serial types if we are updating.
    if ($info['type'] == 'serial' && count($update)) {
      continue;
    }

    // For inserts, populate defaults from Schema if not already provided
    if (!isset($object->$field) && !count($update) && isset($info['default'])) {
      $object->$field = $info['default'];
    }

    // Track serial fields so we can helpfully populate them after the query.
    if ($info['type'] == 'serial') {
      $serials[] = $field;
      // Ignore values for serials when inserting data. Unsupported.
      unset($object->$field);
    }

    // Build arrays for the fields, placeholders, and values in our query.
    if (isset($object->$field)) {
      $fields[] = $field;
      $placeholders[] = db_type_placeholder($info['type']);

      if (empty($info['serialize'])) {
        $values[] = $object->$field;
      }
      else {
        $values[] = serialize($object->$field);
      }
    }
  }

  // Build the SQL.
  $query = '';
  if (!count($update)) {
    $query = "INSERT INTO {" . $table . "} (" . implode(', ', $fields) . ') VALUES (' . implode(', ', $placeholders) . ')';
    $return = SAVED_NEW;
  }
  else {
    $query = '';
    foreach ($fields as $id => $field) {
      if ($query) {
        $query .= ', ';
      }
      $query .= $field . ' = ' . $placeholders[$id];
    }

    foreach ($update as $key) {
      $conditions[] = "$key = " . db_type_placeholder($schema['fields'][$key]['type']);
      $values[] = $object->$key;
    }

    $query = "UPDATE {" . $table . "} SET $query WHERE " . implode(' AND ', $conditions);
    $return = SAVED_UPDATED;
  }

  // Execute the SQL.
  if (db_query($query, $values)) {
    if ($serials) {
      // Get last insert ids and fill them in.
      foreach ($serials as $field) {
        $object->$field = db_last_insert_id($table, $field);
      }
    }
  }
  else {
    $return = FALSE;
  }

  // If we began with an array, convert back so we don't surprise the caller.
  if ($array) {
    $object = (array) $object;
  }

  return $return;
}

Comments

The function cannot be called from hook_install() because Drupal would not find the database schema defined from the module, which is still going to be installed, and enabled.

actually i'm pretty sure i have used it in hook_install(). i seem to remember that after using drupal_install_schema() you needed to use drupal_get_schema() clear the schema cache.

I also tried to use drupal_write_record() in hook_install() and it didn't see to work, even after calling drupal_install_schema() and drupal_get_schema(NULL, TRUE).

drupal_get_schema() calls module_load_all_includes(), which then calls module_list(). When module_list() is invoked, the module (which has not yet completed the installation) is not reported in the modules list, its implementation of hook_schema() will not be called, and Drupal will not have any information about the tables used by the module. In this case, drupal_write_record() doesn't write any data.

This function will also fail from within hook_enable() unless you first call drupal_get_schema().

I'm not sure what the full implications of calling drupal_get_schema() in your hook_enable() or hook_install() really is. My gut says that if something were going to go wrong, it would be when you enable/install more than one module at once (because forcing a rebuild of the schema before all modules have fired their installation hooks might give weird behavior). However, as long as you are only writing to tables that are defined in the same module's hook_schema(), you should be OK (famous last words).

Be careful in combination with db_lock_table(). See http://drupal.org/node/372308 for an explanation and a workaround.

You can use an object such as a node or form results - which may have more columns than your table.

Only the matching properties will be inserted into the table.

As long as your schema matches your form setup in a custom node type you make a hook_insert as simple as

<?php
function mymodule_insert($node){
   
drupal_write_record('mymodule', $node);
}
?>

The extra properties will just be ignored

this function just works with fields that defined by hook_schema if your module attach fields to table this function do not care these fields.

drupal_write_record does not put backticks around column names in MySQL. This means you cant use any MySQL reserved words as column names.

Reading the code makes it obvious if you have a slight knowledge of SQL, but here what's actually required is A key, not necessarily the primary key. Any column or group of columns that have a not null restriction and constitute a unique key will work.

I have discovered that you cannot update a column to NULL using drupal_write_record. This is caused by the test for isset when building the $fields array:

<?php
// Build arrays for the fields, placeholders, and values in our query.
if (isset($object->$field)) {
...
?>

If you try to set a field to NULL then that field never gets into the $fields array and the $query statement does not include it.

Jonathan

If you want to use drupal_write_record() in 'update' mode, in hook_install, hook_update_N, etc. this worked for me!

http://pastebin.com/itHqKVxG

<?php
function hook_update_N() {
 
// Include ALL modules
 
$modules = module_list(TRUE, FALSE);
 
// Set module list as ALL modules
 
module_list(FALSE, TRUE, FALSE, $modules);
 
// Reset implementions list
 
module_implements(NULL, FALSE, TRUE);
 
// Run hook_init
 
module_invoke_all('init');
 
// Reset cached schema
 
drupal_get_schema(NULL, TRUE);

 

drupal_write_record(); // <-- Here your code
}
?>

Unless I've missed something...

In this function an array called serials[] is used to hold all the fields which are of type serial.

Surely there can be only one per table - as defined by MySQL there can be only one auto-increment (serial) field per table. Unless we're planning on using other DB's why are we trying to store more than one serial field.

Please point out what I've missed!

I did not get a proper output initially using this method as I had updated the schema definition in question and changed the Primary Key of the table from auto increment. The MySQL error thrown on the failed insert operation referenced the primary key as auto increment despite having changed it. This occurred as the schema is cached and clearing it before usage did the trick. For people unaware of this hope my experience helps.

I love this method and thought I'd share a recipe for putting into a form submit method to handle both cases of if a user is editing an existing record or creating a new one. This has good error handling and saves you from having to repeat code by having some text snippets in place.

function MYFORM_submit( $form, &$form_state ) {
  $msg = '';
  $msg_type = 'status';
  $redirect = 'admin/MYMODULE/INDEX_PATH';
  $action = 'save your changes';
  $expected = SAVED_UPDATED;
  $update_msg = 'Your changes to the %category category have been saved.';
  if ( is_numeric( $form_state['values']['id'] ) ) {
    $TABLE_DATA['id'] = $form_state['values']['id'];
    $result = drupal_write_record( 'MYTABLE', $TABLE_DATA, 'id' );
  }
  else {
    $result = drupal_write_record( 'MYTABLE', $TABLE_DATA );
    $expected = SAVED_NEW;
    $action = 'create the new category';
    $update_msg = 'The %category category has been created.';
  }
  if ( $result === $expected ) {
    $msg = t( $update_msg, array( '%category' => $TABLE_DATA['category'] ) );
  }
  else {
    $msg_type = 'error';
    $msg = t( "There was a problem when trying to $action. You can " .
        "either try again or let the IT dept. know there's a problem." );
    $redirect = '';
  }
  drupal_set_message( $msg, $msg_type );
  $form_state['redirect'] = $redirect;
}

https://gist.github.com/3919058

Patches welcome! :)

PS: This is specific to a custom module, so you'll have to make a few substitutions, such as MYTABLE & $TABLE_DATA.