61

I want to print the query which is built using db_select() in a programmatical way. Is there any API function provided by Drupal Abstraction Layer?
It is similar to query output in Views, but I want to print it from my custom module for debugging purpose.

67

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.

  • 1
    I think that a function like _get_query_string() should have been part of the SelectQuery interface. – dashohoxha Oct 19 '15 at 20:27
46

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.
  • 1
    Note that this requires the Devel module to be installed. If you do use Devel (I love it), this is the easiest way to go. – joe_flash Feb 20 '14 at 18:14
  • 2
    dpq() where have you been all my life! – Lomax Jun 8 '16 at 21:23
  • Doesn't appear to work in a try catch block when the query fails. So not helpful in my case if I can't debug the broken query. – Kiee Jul 27 '16 at 10:21
19

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());
  • 2
    Short and concise indeed. – dashohoxha Oct 19 '15 at 20:49
  • 2
    No bloat / 3rd party modules required. Plus this works on queries that have not been executed, so you can print a query that fails and gives an error, dpq doesn't seem to allow this even in a try/catch. – Kiee Jul 27 '16 at 10:25
  • 1
    This should be the correct answer. – albertski Apr 25 at 16:28
8

The above answers are good when you have Devel installed and configured.

Best way to print the query without Devel is as below.

$query = db_select('block')
->condition('theme', $theme_key)
->condition('status', 1)
->fields('block');
//One way
echo $query->__toString();
// Second way
echo (string)$query;

We can use one of the above ways to print the query.

4

I have a good solution that you can copy/paste your query string directly in "SQL" section in Phpmyadmin and debug your query ( I often use this method when I struggle with query)

$querystring=$query->__toString();
$querystring=str_replace("{",'',$querystring);
$querystring=str_replace("}",'',$querystring);
foreach($query->getArguments() as $key=> $item){

    if(!$item) {
        $item = 'NULL';
    }
    $querystring=str_replace($key.')',$item.')',$querystring);
}
dpm($querystring);

I hope this be usful for other guys.

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.