You are here

20 April 2012
Drupal SQL query examples, includes regular query and select, insert, update and insert or update using abstraction layer. Explanations of different data return types like fetchassoc, fetchall and fetchcol.

Regular query:

Source code viewer
  1. // Loop through results.
  2. $result = db_query('SELECT * FROM {some_table} WHERE nid = :nid', array(':nid' => $node->nid));
  3. if ($result->rowCount() > 0)
  4. foreach ($result as $row)
  5. print_r($row);
  6. // Get all results using chaining. More fetch possibilities under fetch section below.
  7. $results = db_query('SELECT * FROM {some_table} WHERE nid = :nid AND title LIKE :title', array(':nid' => $nid, ':title' => $title))->fetchAll();
Programming Language: PHP

Database abstraction layer: Select

Select examples:
Source code viewer
  1. // Iterate results through results.
  2. $query = db_select('node', 'n')
  3. ->condition('n.nid', $node->nid)
  4. ->fields('n', array('type', 'title'))
  5. ->execute();
  6. if ($query->rowCount() > 0)
  7. while ($row = $query->fetchAssoc())
  8. dpm($row); // pretty print arrays, devel module
  9.  
  10. // Fetch all results.
  11. $results = db_select('node', 'n')
  12. ->condition('n.nid', $node->nid)
  13. ->fields('n', array('type', 'title'))
  14. ->execute()
  15. ->fetchAll();
  16. // Pretty print arrays, devel module.
  17. dpm($results);
  18.  
Programming Language: PHP
Extend Drupal abstraction layer:
Extend Drupal abstraction layer with common features of sql like range, joins, distinct, functions, etc.
Source code viewer
  1. // JOINS - Using joins, joins are not chainable.
  2. $query = db_select('simplenews_subscription', 'o');
  3. // Use leftJoin for left outer join.
  4. $query->join('simplenews_subscriber', 'e', 'o.snid = e.snid')
  5. ->fields('o', array('tid', 'status'))
  6. ->fields('e', array('snid', 'activated'))
  7. ->condition('e.mail', $email)
  8. ->condition('o.tid', $tid);
  9.  
  10. // Limit / range - Fetch first result in this example. Usually used for performance reasons as well.
  11. $query->range(0, 1);
  12.  
  13. // Remove duplicates - using distinct in Drupal db_select.
  14. $query->distinct();
  15.  
  16. // Functions - You can use sql functions using addExpression ($expression, $alias = NULL, $arguments = array()).
  17. $query->addExpression('MIN(o.tid)', 'min_term_id');
  18.  
  19. // In array condition (in, not in).
  20. $query->condition('n.nid', array(1, 23, 43, 55), 'IN');
  21.  
  22. // Like condition (like, not like).
  23. $query->condition('n.title', 'The%', 'LIKE');
  24.  
  25. // Regular expression as "where" condition.
  26. $query->condition('u.name', '^info[0-9]+$', 'REGEXP');
  27.  
  28. // OR / AND conditions
  29. $db_or = db_or();
  30.  
  31. $db_or->condition('o.created', 1);
  32. $db_or->condition('o.created', 2);
  33.  
  34. $query->condition($db_or);
  35.  
  36. // Recieve the results - more fetch possibilities under fetch section below.
  37. $results = $query
  38. ->execute()
  39. ->fetchAll();
  40. // Pretty print results using devel module.
  41. dpm($results);
Programming Language: PHP

Fetch functions:

Source code viewer
  1. // Fetch next row as a stdClass object
  2. $result = $query->fetchObject();
  3.  
  4. // Fetch next row as an associative array
  5. $result = $query->fetchAssoc();
  6.  
  7. // Fetch next row from specific column (column number argument, defaults to 1)
  8. $result = $query->fetchColumn($column_nr = 1);
  9.  
  10. // Fetch all records as indexed array of stdClass objects
  11. $result = $query->fetchAll();
  12.  
  13. // Fetch all records as accociative array of stdClass objects (column name of keys as argument)
  14. $result = $query->fetchAllAssoc($key);
  15.  
  16. // Fetch all rows from specific column (column number argument, defaults to 1)
  17. $result = $query->fetchCol($column_nr = 1);
  18.  
  19. // Fetch all results, some as keys (arguments specify which columns use as keys and which as values)
  20. $result = $query->fetchAllKeyed($key_index = 0, $value_index = 1);
Programming Language: PHP

Load entity by field value:

Load enitity by field value can be achieved using EntityFieldQuery.
Source code viewer
  1. $query = new EntityFieldQuery();
  2.  
  3. $query->entityCondition('entity_type', 'node')
  4. ->entityCondition('bundle', 'gallery')
  5. ->propertyCondition('status', 1)
  6. ->fieldCondition('field_pictures', 'value', $entity->item_id)
  7. ->fieldCondition('field_multivalue', 'value', $array, 'IN')
  8. ->range(0, 1);
  9.  
  10. $result = $query->execute();
  11.  
  12. if (isset($result['node'])) {
  13. $nids = array_keys($result['node']);
  14. $gallery = node_load($nids[0]);
  15. // Pretty print using devel module.
  16. dpm($gallery);
  17. }
Programming Language: PHP

Database abstraction layer: Insert

Return value of the db_insert function is the auto-increment field's value.
Source code viewer
  1. db_insert('social_networks')->fields(array(
  2. 'uid' => 1,
  3. 'changed' => REQUEST_TIME,
  4. 'data' => $data,
  5. ))
  6. ->execute();
Programming Language: PHP

Database abstraction layer: Update

Source code viewer
  1. // Update, in this example we publish node which id is $node_id.
  2. $count = db_update('node')
  3. ->fields(array(
  4. 'status' => 1,
  5. ))
  6. ->condition('nid', $node_id)
  7. ->execute();
  8.  
  9. // Using mysql functions or custom code in db_update.
  10. $count = db_update('field_data_body')
  11. ->expression('body_value', 'REPLACE(body_value, :find, :replace)', array(
  12. ':find' => $find,
  13. ':replace' => $replace
  14. ))
  15. ->condition('body_value', '%' . $find . '%', 'LIKE')
  16. ->execute();
Programming Language: PHP

Database abstraction layer: Insert or update

Saves (inserts or updates) a record to the database based upon the schema. Returns last inserted ID (Primary key).
Source code viewer
  1. $record = array(
  2. 'changed' => REQUEST_TIME,
  3. 'data' => $data,
  4. );
  5. drupal_write_record('social_networks', $record);
  6.  
  7. $return = $record['uid'];
Programming Language: PHP

Database abstraction layer: Delete

Deletes rows from database. Returns count of deleted rows.
Source code viewer
  1. $num_deleted = db_delete('node')
  2. ->condition('nid', $nid)
  3. ->execute();
Programming Language: PHP