Make a direct database query

Question

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.

Answer 1

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