Using “IN” with db_query()

Question

First off, let me say that I've tried the Drupal 6 answer shown in Using IN clause in db_query and it doesn't work.

I have a query that previously only needed to include items from a single taxonomy id:

$images = db_query("SELECT n.nid, n.title, f.filepath, 
cti.field_iv_source_name_value AS source, bf.nid AS flagged 
FROM node n 
JOIN content_type_imagevault cti ON n.nid = cti.nid 
JOIN content_field_att_file cfaf ON cfaf.nid = n.nid 
JOIN files f ON cfaf.field_att_file_fid = f.fid 
JOIN term_node tn ON tn.nid = n.nid 
LEFT JOIN betterflag bf ON (n.nid = bf.nid AND bf.uid = %d) 
WHERE n.type = 'imagevault' AND tn.tid = %d AND n.status = 1 
ORDER BY n.created DESC LIMIT %d", $user->uid, 134, $displays_count); 

The requirement has changed and we now need to pull images from multiple taxonomy ids. Based on the linked post above, I tried this:

$tax_ids = array(134, 139);
$placeholders = db_placeholders($tax_ids);
$images = db_query("SELECT n.nid, n.title, f.filepath,
cti.field_iv_source_name_value AS source, bf.nid AS flagged
FROM node n
JOIN content_type_imagevault cti ON n.nid = cti.nid
JOIN content_field_att_file cfaf ON cfaf.nid = n.nid
JOIN files f ON cfaf.field_att_file_fid = f.fid
JOIN term_node tn ON tn.nid = n.nid
LEFT JOIN betterflag bf ON (n.nid = bf.nid AND bf.uid = %d)
WHERE n.type = 'imagevault' AND tn.tid IN ($placeholders) AND n.status = 1
ORDER BY n.created DESC LIMIT %d", $user->uid, $tax_ids, $displays_count); 

I just get a blank screen there. When I turn on Devel and display the query log, what it shows as running is:

SELECT n.nid, n.title, f.filepath, cti.field_iv_source_name_value AS source, bf.nid AS flagged 
FROM node n 
JOIN content_type_imagevault cti ON n.nid = cti.nid 
JOIN content_field_att_file cfaf ON cfaf.nid = n.nid 
JOIN files f ON cfaf.field_att_file_fid = f.fid 
JOIN term_node tn ON tn.nid = n.nid 
LEFT JOIN betterflag bf ON (n.nid = bf.nid AND bf.uid = 1) 
WHERE n.type = 'imagevault' AND tn.tid IN (1,5) AND n.status = 1 
ORDER BY n.created DESC LIMIT 0

I have no idea where the 1,5 is coming from in the rendered query. What do I need to do to make this work? Does it only work for nid when selecting directly from a node? If so, how can I use an array in the query? Do I skip the placeholder for that param and just put it directly in the WHERE clause?

Updated info

So it appears that completely aside from the strange case of 134, 139 being translated to 1,5, I also have another issue. Although the variable $displays_count is set further up the page to 5, it's still translating in the query to 0. (I've echoed the value out right before the query, and it's still 5 there, so I don't know what's happening or why changing the tn.tid placeholder would have any effect on the limit placeholder.)

$tax_ids = array(134, 139);
$params = $user->uid;
$params[] = $tax_ids;
$params[] = $displays_count;

$images = db_query(db_rewrite_sql("SELECT n.nid, n.title, f.filepath,
cti.field_iv_source_name_value AS source, bf.nid AS flagged
FROM node n
JOIN content_type_imagevault cti ON n.nid = cti.nid
JOIN content_field_att_file cfaf ON cfaf.nid = n.nid
JOIN files f ON cfaf.field_att_file_fid = f.fid
JOIN term_node tn ON tn.nid = n.nid
LEFT JOIN betterflag bf ON (n.nid = bf.nid AND bf.uid = %d)
WHERE n.type = 'imagevault' AND tn.tid IN (" . db_placeholders($tax_ids) .") AND n.status = 1
ORDER BY n.created DESC LIMIT %d"), $params);

Answer 1

The last three arguments you are passing to db_query() are wrong; you should merge them in an array, and pass that instead of them.

block_list() contains the following code:

$result = db_query(db_rewrite_sql("SELECT DISTINCT b.* FROM {blocks} b LEFT JOIN {blocks_roles} r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = '%s' AND b.status = 1 AND (r.rid IN (" . db_placeholders($rids) . ") OR r.rid IS NULL) ORDER BY b.region, b.weight, b.module", 'b', 'bid'), array_merge(array($theme_key), $rids));

Similarly, blogapi_mt_validate_terms() contains the following code:

  $term_list = array_unique($node->taxonomy);
  $params = $term_list;
  $params[] = $node->type;
  $result = db_query(db_rewrite_sql("SELECT t.tid, t.vid FROM {term_data} t INNER JOIN {vocabulary_node_types} n ON t.vid = n.vid WHERE t.tid IN (" . db_placeholders($term_list) . ") AND n.type = '%s'", 't', 'tid'), $params);

In your specific case, your code should be similar to the following one:

$tax_ids = array(134, 139);
$placeholders = db_placeholders($tax_ids);
$args = array_merge(array($user->uid), $tax_ids, array($displays_count));

/**
 * Alternatively the previous three lines could be replaced by the following ones:
 * $args = array(134, 139);
 * $placeholders = db_placeholders($args);
 * array_unshift($args, $user->uid);
 * $args[] = $displays_count;
 */

$images = db_query("SELECT n.nid, n.title, f.filepath,
  cti.field_iv_source_name_value AS source, bf.nid AS flagged
  FROM node n
  JOIN content_type_imagevault cti ON n.nid = cti.nid
  JOIN content_field_att_file cfaf ON cfaf.nid = n.nid
  JOIN files f ON cfaf.field_att_file_fid = f.fid
  JOIN term_node tn ON tn.nid = n.nid
  LEFT JOIN betterflag bf ON (n.nid = bf.nid AND bf.uid = %d)
  WHERE n.type = 'imagevault' AND tn.tid IN ($placeholders) AND n.status = 1
  ORDER BY n.created DESC LIMIT %d", $args); 

Answer 2

For now, since we only have two options, I've decided to just go with an OR clause, instead of trying to deal with the IN.

$images = db_query("SELECT n.nid, n.title, f.filepath, cti.field_iv_source_name_value AS source, bf.nid AS flagged 
FROM node n 
JOIN content_type_imagevault cti ON n.nid = cti.nid 
JOIN content_field_att_file cfaf ON cfaf.nid = n.nid 
JOIN files f ON cfaf.field_att_file_fid = f.fid 
JOIN term_node tn ON tn.nid = n.nid 
LEFT JOIN betterflag bf ON (n.nid = bf.nid AND bf.uid = %d) 
WHERE n.type = 'imagevault' AND (tn.tid = %d OR tn.tid = %d) AND n.status = 1 
ORDER BY n.created DESC LIMIT %d", $user->uid, 134, 139, $displays_count);