Database Quick Code

Action	Query
STATIC QUERY EXAMPLE	// Framing Query. $query = "SELECT nid, title FROM {node} WHERE type = :type"; $placeholders = [   ':type' => 'article', ]; // Query Result. $records = db_query($query, $placeholders); foreach ($records as $record) {   echo $record->nid;   echo $record->title; } Note: Static queries should be used when the query string is constant(without variables), and is not required to get altered. And specifically with SELECT query, 90% use-cases use static-queries for best performance. In static queries, all table names must be wrapped in {}, which helps database system to attach a prefix string to them if appropriate. Prefixing allows for running multiple sites from the same database or, in limited cases, for sharing selected tables between sites. Placeholders should not be escaped or quoted regardless of their type. Also, placeholders beginning with "db_" are reserved for internal system use.
DYNAMIC QUERY EXAMPLE	$table = 'node'; $alias = 'n'; $columns = [   'nid',   'title', ]; // WHERE clause condition. $field = 'type'; $value = 'article'; $query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($field, $value); // Query result. $records = $query->execute(); foreach ($records as $record) {   echo $record->nid;   echo $record->title; }
SELECT QUERY [Dynamic]	$query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($field, $value, $operator); $records = $query->execute(); // It also allows $options to specify target database, fetch method etc.. $options = [   'target' => 'slave',   'fetch' => PDO::FETCH_ASSOC, ]; db_query($query, $placeholders, $options);
MANAGE COLUMNS/FIELDS [Dynamic/Chainable]	// Fetch multiple fields. $query = db_select($table, $alias)   ->fields($alias, $columns); // Fetch all fields. $query = db_select($table, $alias)   ->fields($alias);
ADD COLUMN/FIELD [Dynamic/Non-Chainable]	$field = 'title'; $field_alias = 'my_title'; $query = db_select($table, $alias); // Returns $field_alias OR alias used if automatically generated. $alias_used = $query->addField($alias, $field, $field_alias); // Note: addField() does not support chaining, so they have to be called separately.
BASIC WHERE CLAUSE OPERATORS [Dynamic/Chainable]	$field = 'type'; $value = 'article'; $operator = '='; $query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($field, $value, $operator); // Supporting operators =, !=, <>, <, >, <=, >=, LIKE, IN, NOT IN, BETWEEN. // Note: Calling condition($field, $value) is equivalent to condition($field, $value, '=').
RAW WHERE CLAUSE [Dynamic/Chainable]	$snippet = "type = :type"; $placeholders = [   ':type' => 'article', ]; $query = db_select($table, $alias)   ->fields($alias, $columns)   ->where($snippet, $placeholders);
AND OPERATOR/CONDITION [Dynamic/Chainable]	$query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($field1, $value1, $operator1)   ->condition($field2, $value2, $operator2); // Another way. $and_condition = db_and()   ->condition($field1, $value1, $operator1)   ->condition($field2, $value2, $operator2); $query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($and_condition);
OR OPERATOR/CONDITION [Dynamic/Chainable]	$or_condition = db_or()   ->condition($field1, $value1, $operator1)   ->condition($field2, $value2, $operator2); $query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($or_condition);
XOR OPERATOR/CONDITION [Dynamic/Chainable]	// Condition is TRUE if one or the other but not both is TRUE. $xor_condition = db_xor()   ->condition($field1, $value1, $operator1)   ->condition($field2, $value2, $operator2); $query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($xor_condition);
NULL CONDITION [Dynamic/Chainable]	$query->isNull($field); $query->isNotNull($field);
LIKE OPERATOR [Dynamic/Chainable]	$field = 'type'; $value = 'artic'; $operator = 'LIKE'; $query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($field, '%' . db_like($value) . '%', $operator);
DISTINCT CLAUSE [Dynamic/Chainable]	$query = db_select($table, $alias)   ->fields($alias, $columns)   ->distinct();
ORDER BY CLAUSE [Dynamic/Chainable]	$sort_ascending = 'ASC'; $sort_descending = 'DESC'; $query = db_select($table, $alias)   ->fields($alias, $columns)   ->orderBy($field1, $sort_ascending)   ->orderBy($field2, $sort_descending); // Random Order ASC/DESC. $query->orderBy($field)->orderRandom();
GROUP BY CLAUSE [Dynamic/Chainable]	$query = db_select($table, $alias)   ->fields($alias, $columns)   ->groupBy($field1)   ->groupBy($field2);
HAVING CLAUSE [Dynamic/Chainable]	// The Having clause behaves identically to the WHERE clause, except that it uses methods havingCondition() and having() instead of condition() and where(). $snippet = 'COUNT(nid) >= :count'; $placeholders = [   ':count' => 10, ]; $query->having($snippet, $placeholders); // Another way. $query->havingCondition($field, $value, $operator); // Note: First parameter to having is not filtered before sending it to the database, so user supplied values should be passed in via the second parameter.
EXPRESSIONS [Dynamic/Non-Chainable]	// Return value of addExpression() will be the alias used which is mostly $field_alias only. // New incremented salary. $expression = 'salary + (salary * :increment/100)'; $field_alias = 'new_salary'; $placeholders = [   ':increment' => '30', ]; $alias_used = $query->addExpression($expression, $field_alias , $placeholders); // Supporting functions: MAX, MIN, SUM, AVG, COUNT, CONCAT, SUBSTRING, LENGTH, GREATES, POW, LOG, CASE etc.. // It is up to the module developer to ensure that only cross-database compatible expressions are used. // Note: addExpression() does not support chaining, so they have to be called separately.
IN OPERATOR [Static/Dynamic]	// Static Query. $query = "SELECT * FROM {node} WHERE nid IN (:nids)"; $placeholders = [   ':nids' => [11, 22, 33], ]; // Dynamic Query. $operator = 'IN'; $value = [11, 22, 33]; $query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($field, $value, $operator); // Static Query. $query = "SELECT * FROM {node} WHERE nid IN (:nids[])"; $placeholders = [   ':nids[]' => [11, 22, 33], ]; // Dynamic Query. $operator = 'IN'; $value = [11, 22, 33]; $query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($field, $value, $operator);
NOT IN OPERATOR [Static/Dynamic]	// Static Query. $query = "SELECT * FROM {node} WHERE nid NOT IN (:nids)"; $placeholders = [   ':nids' => [11, 22, 33], ]; // Dynamic Query. $operator = 'NOT IN'; $value = [11, 22, 33]; $query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($field, $value, $operator); // Static Query. $query = "SELECT * FROM {node} WHERE nid NOT IN (:nids[])"; $placeholders = [   ':nids[]' => [11, 22, 33], ]; // Dynamic Query. $operator = 'NOT IN'; $value = [11, 22, 33]; $query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($field, $value, $operator);
BETWEEN OPERATOR [Static/Dynamic]	// Static Query. $query = "SELECT * FROM {node} WHERE nid BETWEEN :nid1 AND :nid2"; $placeholders = [   ':nid1' => 11,   ':nid2' => 33, ]; // Dynamic Query. $operator = 'BETWEEN'; $value = [11, 33]; $query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($field, $value, $operator);
INSERT QUERY [Dynamic]	$values = [   $field1 => $value1,   $field2 => $value2, ]; // Returns untrusted value; or AUTO-INCREMENT(serial type) field value. $untrusted = db_insert($table)   ->fields($values)   ->execute(); // Note: Insert queries must always use a query builder object(layer of abstraction), allowing individual database drivers special handling for column values (if applicable), example case for LOB and BLOB fields.
MULTIPLE INSERT QUERY [Dynamic]	$columns = [   $field1,   $field2, ]; $values = [   [     $field1 => $value1_1,     $field2 => $value2_1,   ],   [     $field1 => $value1_2,     $field2 => $value2_2,   ], ]; $query = db_insert($table)->fields($columns); foreach ($values as $record) {   $query->values($record); } // Returns untrusted value; or very first AUTO-INCREMENT(serial type) field value. $untrusted = $query->execute();
INSERT QUERY (USING DEFAULT VALUES) [Dynamic/Chainable]	// Use all default values for the entire record. // Provided columns should have a DEFAULT VALUE defined in database. $columns = [   'name',   'city', ]; // Returns untrusted value; or AUTO-INCREMENT(serial type) field value. $untrusted = db_insert($table)   ->useDefaults($columns)   ->execute();
INSERT QUERY (USING SELECT QUERY RESULT) [Dynamic/Chainable]	$query = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($field, $value, $operator); // Returns untrusted value; or AUTO-INCREMENT(serial type) field value. $untrusted = db_insert($insert_in_table)   ->from($query)   ->execute();
UPDATE QUERY [Dynamic]	$values = [   $field1 => $value1,   $field2 => $value2, ]; // Returns updated rows count. $row_count = db_update($table)   ->fields($values)   ->condition($field, $value, $operator)   ->execute(); // It also supports where() with raw conditions. $snippet = "type = :type"; $placeholders = [   ':type' => 'article', ]; $row_count = db_update($table)   ->fields($values)   ->where($snippet, $placeholders)   ->execute();
DELETE QUERY [Dynamic]	// Returns updated rows count. $row_count = db_delete($table)   ->condition($field, $value)   ->execute(); // Note: Without condition it will delete all the rows.
TRUNCATE QUERY	db_truncate($table)->execute();
MERGE QUERY [Dynamic]	// In Merge, if a given condition is met, then an UPDATE will run, else INSERT will run. // Also called as UPSERT QUERY, i.e., UPDATE OR INSERT. $if_column_values_exists = [   'title' => $title, ]; $values = [   $field1 => $value1,   $field2 => $value2, ]; // Returns 1 if data inserted OR 2 if data updated. $merge_type = db_merge($table)   ->key($if_column_values_exists)   ->fields($values)   ->execute();
CONDITIONAL MERGE QUERY [Dynamic]	// Unlike Simple Merge, conditional Merge helps in managing different sets of field values specific to UPDATE OR INSERT query. $insert_values = [   $field1 => $value1,   $field2 => $value2, ]; $update_values = [   $field3 => $value3, ]; // Returns 1 if data inserted OR 2 if data updated. $merge_type = db_merge($table)   ->key($if_column_values_exists)   ->insertFields($insert_values)   ->updateFields($update_values)   ->execute();
JOINS [Dynamic/Non-Chainable]	$table = 'node'; $join_table = 'user'; $alias = 'n'; $join_alias = 'u'; $join_on = 'n.uid = u.uid AND u.uid = :uid'; $placeholders = [   ':uid' => 1, ]; $query = db_select($table, $alias); // Returns $join_alias OR alias used if automatically generated. $alias_used = $query->join($join_table, $join_alias, $join_on, $placeholders); $query->fields($alias, $columns)   ->condition($field, $value, $operator); // Join also works with another query result. $resultant_table = db_select($table, $alias)   ->fields($alias, $columns)   ->condition($field, $value, $operator); $query = db_select($new_table, $new_alias); $query->join($resultant_table, $resultant_alias, $join_on); // Types of joins: join()/innerJoin(), leftJoin(), rightJoin(). // Note: Joins does not support chaining, so they have to be called separately.
UNION QUERY [Dynamic/Chainable]	$append_table = db_select($table2, $alias2)   ->fields($alias2, $columns2); $query = db_select($table1, $alias1)   ->fields($alias1, $columns1)   ->union($append_table);
SUB-QUERY [Dynamic]	$subquery = db_select($table, $alias)   ->fields($alias, [$column]); $query->condition('sl.nid', $subquery, 'NOT IN'); // Note: Subquery must contain single-column passed within an array.
LIMITING RESULTS [Dynamic/Chainable]	$query = db_select($table, $alias)   ->fields($alias, $columns)   ->range($offset, $limit);
COUNT QUERY [Static/Dynamic/Chainable]	// For Static Query. $row_count = db_query($query, $placeholders)   ->rowCount(); // For Dynamic Query. $row_count = db_select($table, $alias)   ->fields($alias, $columns)   ->execute()   ->rowCount(); // Another way. $row_count = db_select($table, $alias)   ->countQuery()   ->execute()   ->fetchField();
ADD TAG (QUERY TAGGING) [Dynamic/Chainable]	$tag = 'tag_name'; $query = db_select($table, $alias)   ->fields($alias, $columns)   ->addTag($tag); // Other query tag supporting functions.. // Returns TRUE if this query object has specified tag. $query->hasTag('tag_name'); // Returns TRUE if this query object has all specified tags. $query->hasAllTags('tag_name1', 'tag_name2'); // Returns TRUE if query object has at least one of the specified tags. $query->hasAnyTag('tag_name1', 'tag_name2'); // Tagging add scope for other modules to alter query on the fly. // Also check hook_query_alter($query), hook_query_TAG_NAME_alter($query)
ADD META DATA (QUERY TAGGING) [Dynamic/Chainable]	// Metadata exists to provide additional information to alter hooks, and generally only apply when the query has certain tags. $node = node_load($nid); $query = db_select($table, $alias)   ->fields($alias, $columns)   ->addMetaData('my_data', $node); // Supporting functions.. // Returns exiting MetaData value OR NULL if key not exists. $node = $query->getMetaData('my_data'); // Also check: hook_query_alter($query), hook_query_TAG_NAME_alter($query)
HOOK QUERY ALTER	// All Dynamic Select query objects are passed through hook_query_alter() by the execute() method, immediately before the query string is compiled. // The alter hook may take any action on the query object it wishes, except executing the query again as that would result in an infinite loop. // Call Order: hook_query_alter($query) >> hook_query_TAG_NAME_alter($query) // Supporting functions to alter the query.. $query->hasTag('example'); $query->hasAllTags('example1', 'example2'); $query->hasAnyTag('example1', 'example2'); $node = $query->getMetaData('node'); $fields =& $query->getFields(); $expressions =& $query->getExpressions(); $tables =& $query->getTables(); $order =& $query->getOrderBy(); $where =& $query->conditions(); $having =& $query->havingConditions(); $group =& $query->getGroupBy(); // Altering ORDER BY. $order =& $query->getOrderBy(); unset($order['n.nid']);
DB TRANSACTION	// Transaction starts here.. $transaction = db_transaction(); try {   $id = db_insert($table)     ->fields($values)     ->execute();   //throw new Exception("drupaldrug");   return $id; } catch (Exception $e) {   $transaction->rollback();   watchdog_exception('drupaldrug', $e); } // Transaction ends here.. // $transaction goes out of scope here. Unless it was rolled back, it gets automatically commited here. // If your transaction is nested inside of another then Drupal will track each transaction and only commit the outer-most transaction when the last transaction object goes out out of scope, that is, all relevant queries completed successfully. // Note: You must assign the return value of db_transaction() to a variable, as in the example. If you call the function without assigning the return value to a variable, your transaction will commit instantly, making it useless.
EXTENDERS [Dynamic/Non-Chainable]	// You can think of that extenders as an implementation of the Decorator Pattern. $extender = 'PagerDefault'; $query = $query->extend($extender); // Supports PagerDefault, TableSort, SearchQuery, MigrateConnectionQuery etc. // Note: Extenders do not support chaining, so they have to be called separately. // Important: extend() returns a new object, if not saved to a variable it will be lost.
RESULTS [Static/Dynamic]	// Static Query. $records = db_query($query, $placeholders); // Dynamic Query. $records = $query->execute(); // Iterating static/dynamic query records using FOREACH LOOP. foreach ($records as $record) {   echo $record->nid;   echo $record->title; }
FETCHING NEXT RECORD (EXPLICITLY) [Static/Dynamic/Chainable]	// Returns record as an stdClass object. $record = $records->fetch(); $record = $records->fetchObject(); // Iterating each record explicitly. while ($record = $records->fetch()) {   echo $record->nid;   echo $record->title; } // Returns record as an associative array. $record = $records->fetchAssoc(); // Iterating each record explicitly. while ($record = $records->fetchAssoc()) {   echo $record['nid'];   echo $record['title']; } // Returns record as a single field value associative array. // Column index within the query being fetched. $field_index = 1; $field = $records->fetchField($field_index); // Iterating each record explicitly. while ($field = $records->fetchField($field_index)) {   echo $field; } // Note: Calling fetchField() is equivalent to fetchField(0). // Note: fetch() uses default fetch mode and could be with other modes as well eg., fetch(PDO::FETCH_NAMED).
FETCHING ALL RECORDS AT ONCE [Static/Dynamic/Chainable]	// Returns records as an array of stdClass objects. $formatted_records = $records->fetchAll(); // Note: fetchAll() uses default fetch mode and could be with other modes as well eg., fetchAll(PDO::FETCH_NAMED). // Returns records as an associative array indexed by the field in the result mentioned. $field = 'nid'; $formatted_records = $records->fetchAllAssoc($field); // Note: fetchAllAssoc() could be with other modes as well eg., fetchAllAssoc($field, PDO::FETCH_NAMED). // Returns 2-column records as an associative array mapped like $field1 => $field2. $field_index1 = 0; $field_index2 = 2; $formatted_records = $records->fetchAllKeyed($field_index1, $field_index2); // Note: Calling fetchAllKeyed() is equivalent to fetchAllKeyed(0, 0). // Returns 1-column records as an associative array with respective field values. $field_index = 2; $formatted_records = $records->fetchCol($field_index); // Note: Calling fetchCol() is equivalent to fetchCol(0).
SETTINGS.PHP (CONFIGURATION)	$host = 'localhost'; // Find other supporting drivers at DRUPAL_ROOT/includes/database. $driver = 'mysql'; // Optional PDO options. $pdo_options = [   'PDO::MYSQL_ATTR_COMPRESS' => 1, ]; $db_params = [   'driver' => $driver,   'database' => 'my_master_db',   'username' => 'my_db_username',   'password' => 'my_db_password',   'host' => $host,   'pdo' => $pdo_options, ]; // Set default master database. $key = 'default'; $target = 'default'; $databases[$key][$target] = $db_params; // Set default slave1 database. $target = 'slave'; $db_params['database'] = 'my_slave1_db'; $db_params['host'] = 'my_slave1_ip'; $databases[$key][$target][] = $db_params; // Set default slave2 database. $target = 'slave'; $db_params['database'] = 'my_slave2_db'; $db_params['host'] = 'my_slave2_ip'; $databases[$key][$target][] = $db_params; // Managing custom/other databases. $key = 'my_key'; $target = 'default'; $databases[$key][$target] = [   'driver' => 'sqlite',   'database' => 'files/my_db.sqlite', ]; // Note: Each driver may have a different $db_params configurations.
DATABASE CONNECTION	// Get connection object. $key = 'my_key'; $target = 'slave'; $conn = Database::getConnection($target, $key); // Below to provide connection object for $target = 'default' of the active connection. $conn = Database::getConnection(); // Note: A database connection is not opened (the connection object is not created) until the first time some piece of code tries to run a query against that database.
SWITCH DATABASE (ON THE FLY) [Dynamic]	// Requires database(need to be switched) settings available in settings.php. // Check SETTINGS.PHP CONFIG for more. // Below query will target default database. $records = $query->execute(); // Switch to new-database specifiying $key. $key = 'my_key'; db_set_active($key); $new_dbrecords = $new_dbquery->execute(); // Go back to the default database. db_set_active(); // Note: Calling db_set_active() is equivalent to db_set_active('default').
SETTING AND SWITCHING DATABASE (ON THE FLY)	// Database(need to be switched) settings NOT available in settings.php. // Check SETTINGS.PHP CONFIG for more. // Below query will target default database. $records = $query->execute(); // Initiate new connection information. $db_params = [   'driver' => $driver,   'database' => 'my_master_db',   'username' => 'my_db_username',   'password' => 'my_db_password',   'host' => $host, ]; $key = 'my_key'; $target = 'default'; Database::addConnectionInfo($key, $target, $db_params); // Switch to new-database specifiying $key(managed on the fly). db_set_active($key); $new_dbrecords = $new_dbquery->execute(); // Go back to the default database. db_set_active(); // Note: Calling db_set_active() is equivalent to db_set_active('default').
CHAINING FUNCTIONS Dynamic	// Query object created without chaining. $query = db_select($table, $alias); $query->fields($alias); $query->range($offset, $limit); // Query object created with chaining. $query = db_select($table, $alias)   ->fields($alias)   ->range($offset, $limit); Reference list of functions that supports chaining: Chainable Non-Chainable addMetaData() addExpression() addTag() addField() comment() addJoin() condition() extend() countQuery() join() distinct() innerJoin() execute() leftJoin() exists() rightJoin() fields() forUpdate() groupBy() having() havingCondition() isNotNull() isNull() notExists() orderBy() orderRandom() range() union() where()
DEBUG	// To examine the SQL query that the query object uses at a particular point in its lifecycle, print the query object. print_r($query->__toString()); // To examine the arguments(say, value in condition), look at the Array returned by the arguments() method. print_r($query->arguments());

♪ ♫  Finding this helpful? Please put in comments what more could be added here..