Drupal 7 Common Database SQL operations (update, delete, query)

One of the new functions provided by Drupal 7 is the ability to use the Query Builder and Query Objects Query Builder to construct Query Objects without the need to write original SQL statements in the code, one is to improve code readability, and the other is to be compatible with all databases.

Insert a single record

The code is as follows:

Copy code

Db_insert ("table")-> fields (array ('field1' => 'value1', 'field2' => 'value2', 'fielddn '=> $ valuen )) -> execute (); insert multiple records $ Values [] = array ('field1' => 'val1', 'field2' => 'val2', 'fielddn '=> $ valn ); $ Values [] = array ('field1' => 'value1', 'field2' => 'value2', 'fielddn '=> $ valuen ); $ Query = db_insert ('table')-> fields (array ('field1', 'field2', 'fielddn ')); Foreach ($ values as $ record ){ $ Query-> values ($ record ); } $ Query-> execute ();

Update a record

The code is as follows:	Copy code
Db_update ('imports ') -> Condition ('name', 'Chico ') -> Fields (array ('address' => 'Go West St .')) -> Execute ();

Equivalent:

The code is as follows:	Copy code
UPDATE {imports} SET address = 'Go West St. 'WHERE name = 'Chico ';

Delete a record

The code is as follows:	Copy code
Db_delete ('imports ') -> Condition ('name' => 'zeppo ') -> Execute ();

Merge Records

The code is as follows:	Copy code
Db_merge ('people ') -> Key (array ('job' => 'Speaker ')) -> InsertFields (array ('age' => 31, 'name' => 'Meredith ')) -> UpdateFields (array ('name' => 'Www .111cn.net ')) -> Execute ();

If a record whose job is Speaker exists, the name is updated to www.111cn.net. If no record exists, a record with age 31, name Meredith, and job as Speaker is inserted.

Automatically add or auto-increment a field value in the database.

The code is as follows:	Copy code
Db_update ('example _ table ') -> Expression ('count', 'Count + 1 ') -> Condition ('field1', $ some_value) -> Expression ('field2', 'field2 +: Inc', array (': Inc' => 2 )) -> Execute ();

Query a database field as another alias (alias)

The code is as follows:	Copy code
$ Query = db_select ('node', 'n '); $ Query-> addField ('N', 'name', 'label '); $ Query-> addField ('N', 'name', 'value ');

Summary.