[development] Programmatically creating SQL queries

[Larry Garfield]

The answer depends heavily on your Drupal version.

In Drupal 7, there is a dynamic query builder built right into the system for 
exactly that purpose:

http://drupal.org/node/310075

In Drupal 7, that is the one and only way you should be building dynamic 
queries.  (But if you don't need a dynamic query, just use db_query() as it is 
faster.)

In Drupal 6, as another responder said you need to build the SQL string 
yourself and an array of placeholder values, then call db_query() with its 
second form:

$sql = "...";
$values = array(1, 2, 3);
$result = db_query($sql, $values);

You should also look into this function if you need to build, say, an IN 
statement:

http://api.drupal.org/api/drupal/includes--
database.inc/function/db_placeholders/6

(That will wrap in your mail client.  Sorry.)

That is the only way you should be building dynamic queries[1].

--Larry Garfield

[1] Except for a few very strange edge cases where you may need to do 
something else, but odds are if you hit one of those you did something else 
wrong.

On Friday, March 11, 2011 7:33:13 am Andy Fowlston wrote:
> Hi all,
> 
> Is there a preferred way in Drupal to programmatically create SQL queries?
> Currently I'm building a query to pull some webform data from selected
> component fields. I see a few choices:
> 
> 1. Build the query with db_query() placeholders, build an argument array,
> and make the call using call_user_func_array().
> 
> 2. Use sprintf() to build up a complete string, and pass that directly to
> db_query().
> 
> 3. Use other functions to sanitise the arguments.
> 
> Is there any preferred method? Guessing number 2...
> 
> A.
> 
> . . . . . . .
> Andy Fowlston
> +44 (0)20 8747 5068
> andy at pedalo.co.uk
> Skype: andy.pedalo
> www.pedalo.co.uk
> 
> This email is intended only for the above named addressee/s. This email may
> be confidential or legally privileged. If you have received this email and
> you are not a named addressee, you must not use, copy, distribute or
> disclose the email or any part of its contents or take any action in
> reliance on it. If you have received this email in error, please email the
> sender by replying to this message and delete it from your system. All
> reasonable precautions have been taken to ensure no viruses are present in
> this email.
> 
> pedalo limited cannot accept responsibility for loss or damage arising from
> the use of this email or attachments and recommends that you subject these
> to your virus checking procedures prior to use. Any views or opinions
> presented are solely those of the author and not necessarily those of
> Pedalo Limited
> 
> Please consider the environment before printing this email