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. Retrieve entities based on entity properties, field values, and generic entity metadata (bundle, entity type, entity ID, and revision ID). It is not possible to query across multiple entity types. Queries do not automatically respect entity access restrictions. Node access control is performed by the SQL storage engine, but other storage engines might not do this.

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 entity by field value can be achieved using EntityFieldQuery.

Source code viewer

1. // Fetch a single entity.

2. $query = new EntityFieldQuery();

3.  

4. $query->entityCondition('entity_type', 'node')

5. ->entityCondition('bundle', 'gallery')

6. ->propertyCondition('status', 1)

7. ->propertyCondition('uid', $uid)

8. ->fieldCondition('field_pictures', 'value', $entity->item_id)

9. ->fieldCondition('field_multivalue', 'value', $array, 'IN')

10. ->range(0, 1);

11.  

12. $result = $query->execute();

13. if (isset($result['node'])) {

14. $nids = array_keys($result['node']);

15. $gallery = node_load($nids[0]);

16. // Pretty print using devel module.

17. dpm($gallery);

18. }

19.  

20. // Fetch multiple entities.

21. $nodes = new EntityFieldQuery();

22. $nodes

23. ->entityCondition('entity_type', 'node')

24. ->entityCondition('bundle', 'model');

25.  

26. $nodes = $query->execute();

27. if (isset($nodes['node'])) {

28. $node_ids = array_keys($nodes['node']);

29. foreach ($node_ids as $nid) {

30. dpm($nodes);

31. }

32. }

Programming Language: PHP

EntityFieldQuery methods for Field Collection module:

Source code viewer

1. $query = new EntityFieldQuery();

2. $query->entityCondition('entity_type', 'field_collection_item');

3.  

4. // No bundle value, use field name

5. $query->propertyCondition('field_name', 'field_appointment_document');

6.  

7. // I'm restricting to search items attached to something, so filtering by the FC I want to check.

8. $query->propertyCondition('item_id', $doc_fc_ids);

9.  

10. // We don't want revisions!

11. $query->propertyCondition('archived', 0);

12.  

13. // An example to filter by a field attached to the FC.

14. $query->fieldCondition('field_document_type', 'tid', array(123,432));

15.  

16. // No date information or useful other property info, but I want the newest one only.

17. // So highest ID is the newest, and only return one result.

18. $query->propertyOrderBy('item_id', 'DESC');

19. $query->range(0, 1);

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