2

Basically I want to run this query against the current database and retrieve all node titles with node type of foo_type.

SELECT title FROM node WHERE type='foo_type' ORDER BY title ASC

How can I do this using db_query or any similar function? I tried doing the below but $result is empty.

$result = db_query("SELECT title FROM node WHERE type='foo_type' ORDER BY title ASC");

Please note any D6 and D7 differences if there are any.

  • The database documentation for Drupal 6 & 7 is vast - could you explain exactly what you don't understand please? What do you mean by 'empty'? What are you using to determine that? – Clive Oct 6 '14 at 16:03
  • dpm($result); Nothing there. I want to access all node titles with foo_type from $result; – joejam Oct 6 '14 at 16:06
  • And are you using Drupal 6 or 7 to get that result? – Clive Oct 6 '14 at 16:06
  • Drupal 6. But would also like to know how to do this on D7 – joejam Oct 6 '14 at 16:07
  • The problem is that the subject is too vast to sum up in a single sentence, and it is heavily dependent on what you are trying to do. It's like asking "What car should I buy and what are the differences?" It depends, and there are many factors. – coreyp_1 Oct 6 '14 at 16:18
9

For Drupal 6:

$result = db_query("SELECT title FROM node WHERE type = '%s' ORDER BY title ASC", 'foo_type');
$titles = array();
while ($row = db_fetch_object($result)) {
  $titles[] = $row->title;
}

For Drupal 7 you have a bunch of options

Static:

$args = array(':type' => 'foo_type');
$titles = db_query("SELECT title FROM node WHERE type = :type ORDER BY title ASC", $args)->fetchCol();

Dynamic:

$titles = db_select('node')
  ->fields('node', array('title'))
  ->condition('type', 'foo_type')
  ->orderBy('title')
  ->execute()
  ->fetchCol();

And a couple of less-direct methods...

EntityFieldQuery:

$query = new \EntityFieldQuery();
$query->entityCondition('entity_type', 'node')
  ->entityCondition('type', 'foo_type')
  ->propertyOrderBy('title');

$results = $query->execute();
if (!empty($results['node'])) {
  $nodes = node_load_multiple(array_keys($results['node']));
  $titles = array_map(function($x) { return $x->title; }, $nodes);
}

API (deprecated):

$nodes = entity_load('node', FALSE, array('type' => 'foo_type'));
$titles = array_map(function($x) { return $x->title; }, $nodes);  
  • Thanks for this. But on D7, what is that fetchCol() bit for? – joejam Oct 6 '14 at 16:17
  • Clive, good answer. Would you also include a PDO example, just for completeness? – coreyp_1 Oct 6 '14 at 16:19
  • Just a helper method - if your query selects a single column it puts those values directly into a 1-d array for you – Clive Oct 6 '14 at 16:20
  • @corey not sure what you mean - D6 doesn't use PDO and all D7 queries are given to PDO anyway...do you mean a db_select example? – Clive Oct 6 '14 at 16:22
  • 1
    @coreyp_1 Done, I've added a couple of other methods as well – Clive Oct 6 '14 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.