Insert queries

Last updated February 1, 2013. Created by Crell on September 18, 2008.
Edited by peterx, Ralt, kalman.hosszu, Jaypan. Log in to edit this page.

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:

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:

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.

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

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.

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 (serial type in hook_schema()) 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

This is the somewhat more verbose equivalent of the previous query, and will have the exact same result.

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.

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.

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

Drupal 7

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.

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.

db_insert or db_query

This is a commonly asked question. (See the comments on this page.) What are the difference between db_insert and db_query?

db_insert has each column specified as a separate entry in the fields array and the code can clean each column value. db_query has an SQL string with no way of checking individual columns. If you use db_query with placeholders, the code can check the column values but placeholders are just an option, there is no way to ensure your SQL does not contain values not passed through placeholders.

db_insert passes the request through a set of hooks to let other modules check and modify your requests. this is the right way to work with other modules. db_query is slightly faster because db_query does not pass the request through the hooks. You might save processing time but your code will not let other modules help your code.

db_insert is more likely to work with other databases and future versions of Drupal.