Print the query which is built using db_select()

You can use dpq() to display the query, and dpr() to display the result.

  $query = db_select('users','u');
  $query->fields('u');
  $query->condition('u.uid', 1042);
  $result = $query->execute()->fetchAll();

  dpq($query); // Display the query. 
  dpr($result); // Display the query result.

SelectQuery implements SelectQuery::__toString(), which is called in the contexts where a string is required.

Consider the following code.

global $theme_key;

$query = db_select('block')
  ->condition('theme', $theme_key)
  ->condition('status', 1)
  ->fields('block');

print $query;

Its output is the following one.

SELECT block.*
FROM 
{block} block
WHERE  (theme = :db_condition_placeholder_0) AND (status = :db_condition_placeholder_1)

To get the array of arguments used for the query, you can call SelectQuery::arguments().

The following code prints the query and its arguments using the functions made available from the Devel module.

global $theme_key;

$query = db_select('block')
  ->condition('theme', $theme_key)
  ->condition('status', 1)
  ->fields('block');

dpm((string) $query);
dpm($query->arguments());

The Devel module is not necessary, though, and you could drupal_set_message() to show the output. For example, you could use the following function to get a string with the placeholders replaced by their actual values.

function _get_query_string(SelectQueryInterface $query) {
  $string = (string) $query;
  $arguments = $query->arguments();

  if (!empty($arguments) && is_array($arguments)) {
    foreach ($arguments as $placeholder => &$value) {
      if (is_string($value)) {
        $value = "'$value'";
      }
    }

    $string = strtr($string, $arguments);
  }

  return $string;
}

The previous example code I shown would become the following one.

global $theme_key;

$query = db_select('block')
  ->condition('theme', $theme_key)
  ->condition('status', 1)
  ->fields('block');

drupal_set_message(format_string('Query: %query', array('%query' => _get_query_string($query))));

function _get_query_string(SelectQueryInterface $query) {
  $string = (string) $query;
  $arguments = $query->arguments();

  if (!empty($arguments) && is_array($arguments)) {
    foreach ($arguments as $placeholder => &$value) {
      if (is_string($value)) {
        $value = "'$value'";
      }
    }

    $string = strtr($string, $arguments);
  }

  return $string;
}

Notice that SelectQuery::arguments() returns the array of query arguments only when it is called after SelectQuery::__toString(), SelectQuery::compile(), or SelectQuery::execute(); otherwise, SelectQuery::arguments() returns NULL.

You could use a function similar to the following one to get the string query, with the placeholders replaced with the arguments.

Another option is:

global $theme_key;

$query = db_select('block')
  ->condition('theme', $theme_key)
  ->condition('status', 1)
  ->fields('block');

print strtr((string) $query, $query->arguments());