0

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

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); 
  • I'm extremely new to Drupal; I'm editing code that I inherited. Please forgive my denseness, but I'm not entirely sure I understand your answer. I'm updating my original post right now with what my understanding is of what you're saying - could you please confirm? – EmmyS Sep 15 '11 at 17:36
0

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

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.