Insert queries
Insert queries must always use a query builder object. Certain databases require special handling for LOB (Large OBject, such as TEXT on MySQL) and BLOB (Binary Large OBject) fields, so a layer of abstraction is required to allow individual database drivers to implement whatever special handling they require.
Insert queries are started using the db_insert() function as follows:
<?php
$query = db_insert('node', $options);
?>That creates an insert query object that will insert one or more records to the node table. Note that braces are not required around the table name as the query builder will handle that automatically.
The insert query object uses a fluent API. That is, all methods (except execute()) return the query object itself allowing method calls to be chained. In many cases, that means the query object will not need to be saved to a variable at all.
The insert query object supports a number of different usage patterns to support different needs. In general, the workflow consists of specifying the fields that the query will insert into, specifying the values the query will insert for those fields, and executing the query. The most common recommended usage patterns are listed below.
Compact form
The preferred form for most Insert queries is the compact form:
<?php
$nid = db_insert('node')
->fields(array(
'title' => 'Example',
'uid' => 1,
'created' => REQUEST_TIME,
))
->execute();
?>This will result in the equivalent of the following query:
INSERT INTO {node} (title, uid, created) VALUES ('Example', 1, 1221717405);
The above snippet chains together the key parts of the insert process.
<?php
db_insert('node')
?>This line creates a new insert query object for the node table.
<?php
->fields(array(
'title' => 'Example',
'uid' => 1,
'created' => REQUEST_TIME,
))
?>The fields() method takes several forms of parameters, but a single associative array is the most common. The keys of the array are the table columns into which to insert and the values are the corresponding values to insert. That will result in a single insert query against the specified table.
<?php
->execute();
?>The execute() method tells the query to run. Unless this method is called, the query does not execute.
Unlike other methods on the Insert query object, which return the query object itself, execute() returns the value of an auto-increment field that was populated by the Insert query, if any. That's why the return value from it is assigned to $nid in the example above. If there is no auto-increment field, the return value from execute() is undefined and should not be trusted.
In the typical case, this is the preferred format for Insert queries.
Degenerate form
<?php
$nid = db_insert('node')
->fields(array('title', 'uid', 'created'))
->values(array(
'title' => 'Example',
'uid' => 1,
'created' => REQUEST_TIME,
))
->execute();
?>This is the somewhat more verbose equivalent of the previous query, and will have the exact same result.
<?php
->fields(array('title', 'uid', 'created'))
?>When fields() is called with an indexed array instead of an associative array, it sets only the fields (database columns) that will be used in the query without setting any values for them. That is useful for running a multi-insert query later.
<?php
->values(array(
'title' => 'Example',
'uid' => 1,
'created' => REQUEST_TIME,
))
?>This method call specifies an associative array of field names to values to insert into those fields. The values() method may also take an indexed array instead. If an indexed array is used, the order of values must match the order of fields in the fields() method. If an associative array is used, it may be in any order. Generally the associative array is preferred for readability.
This query form is rarely used, as the compact form is preferred. In most cases the only reason to separate fields() and values() is when running a multi-insert query.
Multi-insert form
The Insert query object may also take multiple value sets. That is, values() may be called multiple times to enqueue several insert statements together. Exactly how that happens will depend on the capabilities of the database in question. On most databases, multiple insert statements will be executed together inside a transaction for greater data integrity and speed. On MySQL, it will use MySQL's multi-value insert syntax.
<?php
$values = array(
array(
'title' => '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();
?>The above example will execute three insert statements together as a single unit, using the most efficient method for the particular database driver in use. Note that here we have saved the query object to a variable so that we can loop on $values and call the values() method repeatedly.
In the degenerate case, the above example is equivalent to the following three queries:
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 on a multi-insert query the return value from execute() is undefined and should not be trusted, as it may vary depending on the database driver.
Inserting based on the results of a select query
If you want to populate a table with results from other tables, you either need to SELECT from the source tables, interate over the data in PHP and insert it into the new table, or you can do a INSERT INTO...SELECT FROM query in which every record which is returned from the SELECT query gets fed into the INSERT query.
In this example, we want to build a table "mytable" which has a node id and a user name for all nodes on the system which are of the page type.
Drupal 6
<?php
db_query('INSERT INTO {mytable} (nid, name) SELECT n.nid, u.name FROM {node} n LEFT JOIN {users} u on n.uid = u.uid WHERE n.type = "%s"', array ('page'));
?> Drupal 7
<?php
// Build the SELECT query.
$query = db_select('node', 'n');
// Join to the users table.
$query->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();
?>Delayed queries
Certain databases in certain configurations support the concept of a "delayed" insert. That is, the call to the database will return immediately rather than waiting for the query to execute, and the database server will execute the insert operation at some undefined later point in time. That can offer a performance boost, but may result in data in the database not being "caught up" with all queries run against it. That makes it useful for logging and statistics where some delay is acceptable but ill-suited for most user data.
To flag an Insert query to be delayed, use the delay() method.
<?php
$query->delay();
?>If the database engine in use supports delayed queries, the query will be delayed. If not, this method has no effect. To remove the delay flag, call delay() with a single parameter of FALSE.
Note that if a query is delayed it will not return the created auto-increment ID, as the ID hasn't even been created yet. If you care about the created auto-increment ID, do not use a delayed query.
Default values
In normal circumstances, if you do not specify a value for a given field and a default value is defined by the table's schema then the database will silently insert that default value for you. In some cases, however, you need to explicitly instruct the database to use a default value. That includes if you want to use all default values for the entire record. To explicitly tell the database to use the default value for a given field, there is a useDefaults() method.
<?php
$query->useDefaults(array('field1', 'field2'));
?>This line instructs the the query to use the database-defined defaults for fields field1 and field2. Note that it is an error to specify the same field in both useDefaults() and fields() or values(), and an exception will be thrown.