Common Database Operation instances in Drupal7

Common Database Operation instances in Drupal7

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, first, it improves code readability, and second, it is compatible with all databases.

1. Insert a single record

 

The Code is as follows: db_insert ("table")-> fields (array ('field1' => 'value1', 'field2' => 'value2 ', 'fielddn '=> $ valuen)-> execute ();

 

2. Insert multiple records

 

The Code is as follows: $ 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 ();

 

3. Update a record

The Code is as follows: db_update ('imports ')

-> Condition ('name', 'chico ')

-> Fields (array ('address' => 'go West St .'))

-> Execute ();

// Equivalent:

 

UPDATE {imports} SET address = 'go West St. 'WHERE name = 'chico ';

 

4. delete a record

 

The Code is as follows: db_delete ('imports ')

-> Condition ('name' => 'zeppo ')

-> Execute ();

5. Merge Records

 

The Code is as follows: db_merge ('people ')

-> Key (array ('job' => 'speaker '))

-> InsertFields (array ('age' => 31, 'name' => 'meredith '))

-> UpdateFields (array ('name' => 'Tiffany '))

-> Execute ();

// If a record whose job is Speaker exists, the name is updated to Tiffany. If no record exists, a record with age 31, name Meredith, and job as Speaker is inserted.

 

 

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

 

The Code is as follows: db_update ('example _ table ')

-> Expression ('Count', 'count + 1 ')

-> Condition ('field1', $ some_value)

-> Expression ('field2', 'field2 +: inc', array (': inc' => 2 ))

-> Execute ();

 

7. query a database field as another alias (alias)

 

The Code is as follows: $ query = db_select ('node', 'n ');

$ Query-> addField ('n', 'name', 'label ');

$ Query-> addField ('n', 'name', 'value ');