Print the query which is built using db_select()

Solutions

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

screenshot

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());
Tags: Drupal 7 / Database

Similar questions

Use db_select with multiple conditions of which one needs to apply
My question is not a duplicate and it has complex conditions. Please let me explain. In my second condition, first I need to check if number value is correct (in first condition I checked complete opposite). If so, then check if there's a value in email column. Then show error message if there's a value in email column. My problem is I need to writ...
Adding Node-Fields to query with db_select();
I am building a query using db_select(); Now I need to sort my result by a field that a added using fields. Is there some nicer way than to join field_data_field_fieldname, and revision? I was hoping for something like $query->addFieldData("field_foo", "myfoo"); so i can simply $query->orderBy('myfoo', 'asc'); I am also wondering why there is...
Query with db_select() to select node by id and join field and terms
I am trying to write a query using db_select that selects a particular node by id, and then joins it's field_data_field_projectresources column and also selects the terms from taxonomy (because this is a term_reference field) I have tried numerous attempts but I am finding myself stuck (I didn't even get to the part about joining the dr_taxonomy_te...
Making fields, condition and join dynamic in db_select query
What is the Drupal way of creating queries which are dynamic in nature ? What I mean is for example- In my module I am asking the user to select some content types and the fields associated with each of them. If node type "article" is present the user can select it and also select some fields lets say- field3, field4 - out of all the fields present...
limit db_select query by the value of a specific field
I am trying to build a custom drupal module to return the 'staff' that work at a specific charity when you are on the page of the charity. Both the charities and the staff members exist as separate content types. These content types are updated dynamically from a CRM system. The charities and the staff members both have a common field 'Account ID'....
PHP error 500 when including a left join on a pager query in db_select()
I've got a query which is causing a PHP error 500 whenever I add a left join: PHP Fatal error: Call to a member function orderBy() on a non-object Here's the query: The query is to be used with a table + pager. If I remove the jeftJoin() and respective fields() the query works perfectly. Can anyone shed some light as to why this might be happening?...

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.