Drupal: Return SQL string from db_query

Question

Is it possible to return the actual SQL query as a string from the result of db_query?

Or otherwise take the returned resource ID from db_query and get the SQL string?

---

Edit:

As an addendum, I recently found out about db_queryd() from the Devel module, which echoes the query passed (as well as execute it). Doesn't return the string as this question asked, but really helpful for copying and pasting a complete query.

Answer 1

I don't think it is. However if you are only doing so for the purpose of debugging you can turn on the devel module and that will show you the queries run.

Actually you could just set the variable 'dev_query' to 1 and then access the global array $queries, but I wouldn't recommend it.

Answer 2

Drupal 7, if debug, you could find at \includes\database\database.inc:

function query($query, array $args = array(), $options = array())

$stmt's queryString

or

print_r($stmt->getQueryString());

Answer 3

If you have D7 but don't have Devel to hand, the following snippet could come in useful — it may not handle every type of placeholder however... it currently wrongly assumes all placeholders are strings (which has been fine for my usage).

function stringify_query( $query ){
  $s = preg_replace('/\}|\{/', '', $query->__toString());
  $a = $query->arguments();
  foreach ( $a as $key => $val ) {
    $a[$key] = '\'' . $val . '\'';
  }
  return strtr($s, $a);
}

It also rudely strips out Drupal's curly braces used to handle table prefixes, if you rely on table prefixes then you should find the correct Drupal function to have them replaced correctly.

Answer 4

I would recommend the use of the devel module. There is a setting devel offers which will show all queries run during the generation of a page at the bottom of the page, with data on query execution time and the function that called db_query(). If you have a general idea of what your query will look like or the function that called it, you could search for it within your browser and you can see what was actually send to the database.

Answer 5

Late answer, but you can often turn

$result = db_query($query, $arg1, $arg2);

quickly into

drupal_set_message(sprintf($query, $arg1, $arg2), "status");

And get what you want.

This doesn't help you if you are using an array as your argument to db_query as sprintf doesn't support that, but is often useful in your debugging toolkit.

Answer 6

For those using Drupal 7.x and the Devel module, the correct function to call to output the built SQL statement to the drupal message area is dpq(). It needs to be passed the query object though. e.g.

// to see the built SQL
$query = db_select('node', 'n')->fields('n');    
dpq($query);

// to see the results of the query
$results = $query->execute()->fetchAssoc();
dsm($results);

Hope that can help!

Answer 7

D7 version with devel.

>= PHP 5.4

dpm(str_replace(['{', '}'], '', dpq($query, TRUE)));

< PHP 5.4

dpm(str_replace(array('{', '}'), '', dpq($query, TRUE)));