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

Saves (inserts or updates) a record to the database based upon the schema.

Do not use drupal_write_record() within hook_update_N() functions, since the database schema cannot be relied upon when a user is running a series of updates. Instead, use db_insert() or db_update() to save the record.

Parameters

$table: The name of the table; this must be defined by a hook_schema() implementation.

$record: An object or array representing the record to write, passed in by reference. If inserting a new record, values not provided in $record will be populated in $record and in the database with the default values from the schema, as well as a single serial (auto-increment) field (if present). If updating an existing record, only provided values are updated in the database, and $record is not modified.

$primary_keys: To indicate that this is a new record to be inserted, omit this argument. If this is an update, this argument specifies the primary keys' field names. If there is only 1 field in the key, you may pass in a string; if there are multiple fields in the key, pass in an array.

Return value

If the record insert or update failed, returns FALSE. If it succeeded, returns SAVED_NEW or SAVED_UPDATED, depending on the operation performed.

Related topics

33 calls to drupal_write_record()
block_theme_initialize in modules/block/block.module
Assigns an initial, default set of blocks for a theme.
comment_save in modules/comment/comment.module
Accepts a submission of new or changed comment content.
contact_category_edit_form_submit in modules/contact/contact.admin.inc
Form submission handler for contact_category_edit_form().
DrupalDataApiTest::testDrupalWriteRecord in modules/simpletest/tests/common.test
Test the drupal_write_record() API function.
EntityFieldQueryTestCase::setUp in modules/simpletest/tests/entity_query.test
Sets up a Drupal site for running functional and integration tests.

... See full list

File

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

Code

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

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

  $object = (object) $record;
  $fields = array();

  // Go through the schema to determine fields to write.
  foreach ($schema['fields'] as $field => $info) {
    if ($info['type'] == 'serial') {
      // Skip serial types if we are updating.
      if (!empty($primary_keys)) {
        continue;
      }
      // Track serial field so we can helpfully populate them after the query.
      // NOTE: Each table should come with one serial field only.
      $serial = $field;
    }

    // Skip field if it is in $primary_keys as it is unnecessary to update a
    // field to the value it is already set to.
    if (in_array($field, $primary_keys)) {
      continue;
    }

    if (!property_exists($object, $field)) {
      // Skip fields that are not provided, default values are already known
      // by the database.
      continue;
    }

    // Build array of fields to update or insert.
    if (empty($info['serialize'])) {
      $fields[$field] = $object->$field;
    }
    else {
      $fields[$field] = serialize($object->$field);
    }

    // Type cast to proper datatype, except when the value is NULL and the
    // column allows this.
    //
    // MySQL PDO silently casts e.g. FALSE and '' to 0 when inserting the value
    // into an integer column, but PostgreSQL PDO does not. Also type cast NULL
    // when the column does not allow this.
    if (isset($object->$field) || !empty($info['not null'])) {
      if ($info['type'] == 'int' || $info['type'] == 'serial') {
        $fields[$field] = (int) $fields[$field];
      }
      elseif ($info['type'] == 'float') {
        $fields[$field] = (float) $fields[$field];
      }
      else {
        $fields[$field] = (string) $fields[$field];
      }
    }
  }

  if (empty($fields)) {
    return;
  }

  // Build the SQL.
  if (empty($primary_keys)) {
    // We are doing an insert.
    $options = array('return' => Database::RETURN_INSERT_ID);
    if (isset($serial) && isset($fields[$serial])) {
      // If the serial column has been explicitly set with an ID, then we don't
      // require the database to return the last insert id.
      if ($fields[$serial]) {
        $options['return'] = Database::RETURN_AFFECTED;
      }
      // If a serial column does exist with no value (i.e. 0) then remove it as
      // the database will insert the correct value for us.
      else {
        unset($fields[$serial]);
      }
    }
    $query = db_insert($table, $options)->fields($fields);
    $return = SAVED_NEW;
  }
  else {
    $query = db_update($table)->fields($fields);
    foreach ($primary_keys as $key) {
      $query->condition($key, $object->$key);
    }
    $return = SAVED_UPDATED;
  }

  // Execute the SQL.
  if ($query_return = $query->execute()) {
    if (isset($serial)) {
      // If the database was not told to return the last insert id, it will be
      // because we already know it.
      if (isset($options) && $options['return'] != Database::RETURN_INSERT_ID) {
        $object->$serial = $fields[$serial];
      }
      else {
        $object->$serial = $query_return;
      }
    }
  }
  // If we have a single-field primary key but got no insert ID, the
  // query failed. Note that we explicitly check for FALSE, because
  // a valid update query which doesn't change any values will return
  // zero (0) affected rows.
  elseif ($query_return === FALSE && count($primary_keys) == 1) {
    $return = FALSE;
  }

  // If we are inserting, populate empty fields with default values.
  if (empty($primary_keys)) {
    foreach ($schema['fields'] as $field => $info) {
      if (isset($info['default']) && !property_exists($object, $field)) {
        $object->$field = $info['default'];
      }
    }
  }

  // If we began with an array, convert back.
  if (is_array($record)) {
    $record = (array) $object;
  }

  return $return;
}

Comments

Gribnif’s picture

For a query that will automatically decide whether to insert or update, use db_merge(). See this page: http://drupal.org/node/310085

ste5ens’s picture

While developing a custom module, I noticed drupal_write_record fails silently when the selected table schema is missing from the cache.
I realized a watchdog entry for this event would have saved me some time.

...
if (empty($schema)) {
  watchdog('system', '%table schema not defined.', array('%table' => $table), WATCHDOG_WARNING);
  return FALSE;
}
...
colinmcclure’s picture

Could not agree with you more ste5ens. Ran into the exact problem myself today and was banging my head against it until stumbling across your post! Thank-you very much indeed!

tunic’s picture

SAVE_UPDATED is always returned when an update SQL query is performed, even in case no record is actually modified.

Check this bug report:
http://drupal.org/node/602190

For example, if you provide a wrong primary key value and no record is modified drupal_write_record will returns SAVED_UPDATED.

menelaoEE’s picture

Apparently as of Drupal 7.17, this function return the number of effected rows after an update and not SAVED_UPDATED.
you may read about the relevant changes here
http://drupal.org/node/602190

Gold’s picture

I was having an issue getting drupal_write_record() to insert a new record. Everything was correct from the drupal_write_record() end. I tracked it down to the definition in hook_schema(). Ensure that your tables id field is a 'serial' field. An 'int' field won't auto-increment. Newbie issue, but if you don't spend much time with hook_schema() it's one that may catch you out.

ElusiveMind’s picture

If you are dealing with 64 bit integers and windows, (bigint), AVOID THIS LIKE THE PLAGUE. Instead use db_insert. This function does not properly handle bigint or 64 bit integers. Save yourselves the week it took me to learn this.

mckinzie25’s picture

Looks like you get a PDO error if you use drupal_write_record() and have spaces in your table names (e.g., "IP Address"). I've only checked this on MySQL.