Working with Database in Drupal 7 - Lesson 8 - Insert Queries (INSERT INTO)

Insert queries should always use the query builder. Some databases require special handlers for LOB (Large Object, such as text in MySQL) and BLOB (Binary Large Object) fields, so an abstraction layer is necessary for individual DB drivers to implement these handlers.

Insert queries start with the db_insert() function:

This insert query creates a query object that inserts one or more records into the node table. Note that curly braces are not required—the query builder automatically handles table names.

Insert queries use the fluent API. This means all methods (including execute()) return the object itself, allowing method chaining.

Insert queries support a variety of patterns for different use cases. The common workflow defines the fields and values to be inserted. Common patterns are described below.

Compact Form

The preferred form for most insert queries is the compact form:

fields(array(
    'title' => 'Example',
    'uid' => 1,
    'created' => REQUEST_TIME,
  ))
  ->execute();
?>

This query is equivalent to:

INSERT INTO {node} (title, uid, created) VALUES ('Example', 1, 1221717405);

The snippet binds the keys and inserted values.

This line creates a new insert query object for the node table.

fields(array(
    'title' => 'Example',
    'uid' => 1,
    'created' => REQUEST_TIME,
  ))
?>

The fields() method takes several parameters, most commonly a single associative array. The array keys are the columns to insert into, and the values are the data to insert.

execute();
?>

Unlike other methods, execute() returns the auto-increment value used in the insert. For the node table, this would be the nid. If no auto-increment field exists, the returned value will be undefined and should not be trusted. In normal cases, this is the preferred method for insert queries.

Degenerate Form

fields(array('title', 'uid', 'created'))
  ->values(array(
    'title' => 'Example',
    'uid' => 1,
    'created' => REQUEST_TIME,
  ))
  ->execute();
?>

This query has the same result as the compact form above.

fields(array('title', 'uid', 'created'))
?>

When fields() is called with an indexed array, it defines the columns used in the insert query without assigning values. This is useful for multi-insert queries.

values(array(
    'title' => 'Example',
    'uid' => 1,
    'created' => REQUEST_TIME,
  ))
?>

The values() method provides an associative array of field names and the values to insert. It can also take an indexed array, but in that case the order must match the field list defined earlier. Associative arrays are preferred for readability. Separating fields() and values() is typically only necessary for multi-inserts.

Multi-insert Form

An insert query object can also take multiple sets of values, with values() called repeatedly for batching. This ensures compatibility with DBs and improves performance using bulk inserts.

 'Example',
    'uid' => 1,
    'created' => REQUEST_TIME,
  ),
  array(
    'title' => 'Example 2',
    'uid' => 1,
    'created' => REQUEST_TIME,
  ),
  array(
    'title' => 'Example 3',
    'uid' => 2,
    'created' => REQUEST_TIME,
  ),
);
$query = db_insert('node')->fields(array('title', 'uid', 'created'));
foreach ($values as $record) {
  $query->values($record);
}
$query->execute();
?>

This example inserts three records using the efficient multi-insert syntax. Note that we store the insert object in a variable to iterate and call values() for each set.

This results in:

INSERT INTO {node} (title, uid, created) VALUES ('Example', 1, 1221717405);
INSERT INTO {node} (title, uid, created) VALUES ('Example2', 1, 1221717405);
INSERT INTO {node} (title, uid, created) VALUES ('Example3', 2, 1221717405);

Note that the return value of execute() in multi-inserts is undefined and should not be trusted, as it varies by DB driver.

Insert from SELECT (Summary Table)

To populate a table from another using SELECT, you can either fetch with PHP and insert manually, or use an INSERT INTO ... SELECT style query.

Example: Populate mytable with nid and username from all nodes in the system.

Drupal 6:

Drupal 7:

join('users', 'u', 'n.uid = u.uid');
// Add the fields we want.
$query->addField('n','nid');
$query->addField('u','name');
// Add a condition to only get page nodes.
$query->condition('type', 'page');

// Perform the insert.
db_insert('mytable')
  ->from($query)
  ->execute();
?>

Default Values

Normally, if a value is not provided for a field and the table defines a default, the DB will insert the default silently. However, you can explicitly request default values using useDefaults() when needed (e.g., inserting only defaults).

useDefaults(array('field1', 'field2'));
?>

This forces the query to use default values for field1 and field2. Note: defining the same field in both useDefaults() and fields() or values() will cause an error.