How can I make this query work in D7?

Question

I'm trying to rewrite this database query from the line 52 of my template.php D6 site

  $uid = db_query('SELECT pm.author FROM {pm_message} pm INNER JOIN {pm_index} pmi ON pmi.mid = pm.mid AND pmi.thread_id = %d WHERE pm.author <> %d ORDER BY pm.timestamp DESC LIMIT 1', $thread['thread_id'], $user->uid);

into D7 standards.

But it keeps giving me

Recoverable fatal error: Argument 2 passed to db_query() must be an array, string given, called in C:\wamp2\www\site-name\sites\all\themes\simpler\template.php on line 52 and defined in db_query() (line 2313 of C:\wamp2\www\site-name\includes\database\database.inc).

This DB query is part of a template.php snippet that shows user pictures in Private Messages module, and makes it look like Facebook or other social networking site. You can see the full snippet here. Because Private Messages has a unified value $participants (or the message thread) this DB query is basically trying to isolate the last author except the current user.

What is the correct syntax?

Henrik Opel Henrik Opel · Accepted Answer · 2012-05-04T00:26:53

As the error message says: 'Argument 2 passed to db_query() must be an array ...'.

Drupal 7 switched the database layer to use PDO, so placeholder replacement in db_query() changed a bit - try:

$query = 'SELECT pm.author FROM {pm_message} pm'
  . ' INNER JOIN {pm_index} pmi ON pmi.mid = pm.mid AND pmi.thread_id = :thread_id'
  . ' WHERE pm.author <> :uid'
  . ' ORDER BY pm.timestamp DESC LIMIT 1';
$args = array(
  ':thread_id' => $thread['thread_id'],
  ':uid' => $user->uid,
);
$uid = db_query($query, $args)->fetchField();

Splitted and reformatted for readability. Untested, so beware of typos.

Note the ->fetchField() at the end - this will only work for queries returning exactly one field (like this one). If you need to fetch more fields or records, look at the DatabaseStatementInterface documentation.

How can I make this query work in D7?

1 Answers