Drupal - Dynamic Queries - Part 2

Insert queries

<?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();?>

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);

Update queries

 <?php/* This is a horrible example as node.status is pulled from node_revision.status table as well, updating it here will do nothing. */$num_updated = db_update('node')
  ->fields(array(
    'uid' => 5,
    'status' => 1,
  ))
  ->condition('created', REQUEST_TIME - 3600, '>=')
  ->execute();?>

The above example is equivalent to the following query:

UPDATE {node} SET uid=5, status=1 WHERE created >= 1221717405;

Delete queries

<?php
$num_deleted = db_delete('node')
  ->condition('nid', 5)
  ->execute();?>

The above query will delete all rows from the {node} table where the nid column is 5. It is equivalent to the following query:

DELETE FROM {node} WHERE nid=5;

Chaining

Functions that cannot be chained together have to be done like this:

<?php
$query = db_select('mytable');$query->addField('mytable', 'myfield', 'myalias');$query->addField('mytable', 'anotherfield', 'anotheralias');$result = $query->condition('myfield', 'myvalue')
  ->execute();?>

Functions that can be chained:

• addMetaData()
• addTag()
• comment()
• condition()
• countQuery()
• distinct()
• exists()
• fields()
• forUpdate()
• groupBy()
• having()
• havingCondition()
• isNotNull()
• isNull()
• notExists()
• orderBy()
• orderRandom()
• range()
• union()
• where()
• execute()

Functions that cannot be chained:

• addExpression()
• addField()
• addJoin()
• extend()
• innerJoin()
• join()
• leftJoin()
• rightJoin()