Using db_query in Drupal 7 I'm in the process of porting my contrib module from Drupal 6 to Drupal 7, and I'm getting a little stuck with how I should got about using db_query() in Drupal 7. I know it now uses a similar syntax to PDO, but I'm not quite sure how to translate the following line from the Drupal 6 version of the module:

if ($result = db_query('SELECT name FROM {variable} WHERE name LIKE "%%%s%"', 'csm_node_temp_')) {
while ($array = db_fetch_array($result)) {
  $variables[] = $array['name'];
}


}

This code removes all of the variables that my module has added to the variable table; i.e., all variables with names that start with the string "csm_node_temp". How should I rewrite these lines for the Drupal 7 version of my module?
The D7 equivalent would be:

$sql = 'SELECT name FROM {variable} WHERE name LIKE :name';
$args = array(':name' =&gt; db_like('csm_node_temp_') . '%');

foreach (db_query($sql, $args) as $result) {
  $names[] = $result-&gt;name;
}


Or if you wanted a structured query using db_select():

$variables = db_select('variable', 'v')
  -&gt;fields('v', array('name'))
  -&gt;condition('name', db_like('csm_node_temp_') . '%', 'LIKE');

foreach ($variables-&gt;execute() as $result) {
  $names[] = $result-&gt;name;
}


n.b. the above are the most basic use of either of those functions. With some of the helper methods they get a bit more powerful.

For example, you're just looking for a single column of data from your query. The DB API can handle this with the fetchCol() method. So for the first example you can do:

$sql = 'SELECT name FROM {variable} WHERE name LIKE :name';
$args = array(':name' =&gt; db_like('csm_node_temp_') . '%');
$names = db_query($sql, $args)-&gt;fetchCol();


and for the second:

$names = db_select('variable', 'v')
  -&gt;fields('v', array('name'))
  -&gt;condition('name', db_like('csm_node_temp_') . '%', 'LIKE')
  -&gt;execute()
  -&gt;fetchCol();


In both cases $names will contain an array of the names found by the query. Much easier than looping over the results manually :)