3

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?

6

The D7 equivalent would be:

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

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

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

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

foreach ($variables->execute() as $result) {
  $names[] = $result->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' => db_like('csm_node_temp_') . '%');
$names = db_query($sql, $args)->fetchCol();

and for the second:

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

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

  • I was just writing a comment saying I believe the syntax will still work with D7. D7 added support for named parameters, but as far as I'm aware, doesn't require them. That said, named parameters are easier to read and less error prone because their ordering doesn't matter, so your example is still preferred IMO. (And for something as simple as this, I would think a db_select is overkill, no?) – Letharion Jun 10 '13 at 16:01
  • @Letharion Try running db_query("SELECT * FROM {users} WHERE uid = %d", array(1)) through db_query() in Drupal 7. It'll break nicely :) You can't use the % placeholders in D7 queries, they're just not catered for – Clive Jun 10 '13 at 16:04
  • @Letharion Yeah db_select() is overkill for such a simple query, but the OP's just coming over from D6 so I think it's good for him to know the dynamic option too – Clive Jun 10 '13 at 16:05
  • 1
    You are right of course, it's because one can use ? for unnamed parameters that I just assumed the syntax remained the same. – Letharion Jun 10 '13 at 16:13
  • 1
    @Jim, looks like it yes. Just a small oversight by Clive I believe. – Letharion Jun 10 '13 at 16:30

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.