One of the most used functions in Drupal's database abstraction layer is db_query, which allows passing an SQL string and corresponding arguments to send a query to the database. I'll give you a quick overview of how db_query works before showing you how to drupalize a query such as:

SELECT field1, field2 FROM table_name WHERE field1 IN (value1,value2,...)

<!--break-->

Drupal database abstraction layer and db_query

As many other content management systems and web development frameworks, Drupal implements a database abstraction layer that you, the developer, can use for writing code that works with different database servers. For this to work you need to follow certain rules and this starts with the way you pass queries and arguments to the db_query function.

Arguments in a db_query call need to use sprintf style specifications, placeholders such as %d for integers and %s for strings. This allows Drupal to avoid SQL injection attacks and perform other security checks.

Let's review a simple example:

$sql = "SELECT v.vid FROM {vocabulary} v WHERE v.name = '%s'";
$vid = db_result(db_query($sql, $vocabulary_name));

This code will get a vocabulary's id searching by its name, which is passed as a string. Notice the curly braces around the table's name, they need to be there if you want Drupal to provide table prefixing, this is important so get used to always do it this way.

Many Drupal beginners may opt for what they consider a simpler approach:

$sql = "SELECT v.vid FROM vocabulary v WHERE v.name = '" . $vocabulary_name . "'";
$vid = db_result(db_query($sql));

Even if it may look simpler now you're bypassing Drupal's security checks and your query will start breaking up as a series of concatenated strings which is not good for code readability, and this gets more confusing with more complex queries.

Passing arguments to db_query as an array

Arguments can be passed one by one or contained in an array. Let's slightly modify our example query:

$sql = "SELECT v.vid FROM {vocabulary} v WHERE v.name = '%s' AND v.vid = '%d'";

Now we're being more specific, looking for this vocabulary using v.name and v.vid, pay attention to the placeholders, and we could get our result passing each argument to db_query like this:

$vid = db_result(db_query($sql, $vocabulary_name, $vid));

or we could build an array with both arguments like this:

$args = array($vocabulary_name, $vid);
$vid = db_result(db_query($sql, $args));

I prefer the array approach for queries where I have to pass more than a few arguments and that's often the case when we use the SQL IN operator.

The SQL IN operator and Drupal

Every good Drupal developer has to be highly skilled in writing SQL and that means there will be times when you need the SQL IN operator, which compares a field to a list of values. Let's say you want to get all nodes of types page and blog, you're looking for a query like this:

$sql = "SELECT n.nid, n.title FROM {node} n WHERE n.type IN ('page', 'blog') AND n.status = 1";

If you've tried this before you may have experienced escape quotes hell and opted for the non Drupal way of concatenating arguments in the query, at least I did it until I read about <a href="http://api.drupal.org/api/function/db_placeholders/6db_placeholders.

This is how I build my Drupal queries with the SQL IN operator now:

$types = array('blog', 'embedded_video', 'list', 'node_gallery_gallery');
$args = array();
$args[] = $tid;
$args = array_merge($args, $types);
$args[] = $status;
$args[] = $limit;
$sql = "SELECT n.nid, n.title, n.type, c.comment_count FROM {node} n INNER JOIN {term_node} tn
ON n.nid = tn.nid INNER JOIN {term_data} td ON tn.tid = td.tid LEFT JOIN
{node_comment_statistics} c ON n.nid = c.nid WHERE td.tid = %d AND
n.type IN (" . db_placeholders($types, 'varchar') . ")
AND n.status = %d ORDER BY n.created DESC LIMIT %d";
$result = db_query($sql, $args);

This is a bigger query with more arguments and I'm not only looking for nodes of certain types (listed in the $types array) but also a specific term in the taxonomy ($tid) and a published status ($status). I'm also adding a LIMIT clause at the end ($limit).

db_placeholders takes care of adding the correct number and type of placeholders based on the contents of the array $types. In this case it will add four %s because I passed varchar as the second argument and there are four elements in the array.

The $args array is built based on the order in which the arguments appear in the query, notice how I add $tid first and then use array_merge to add $types, then I add $status and $limit at the end.

So Drupalish, isn't it?