As of 28th November 2014, the video subscription has been shutdown. All videos are available for FREE to watch.

How To Print SQL String Using Devel Module

In Drupal 7 you can query a database table in two ways; using a static query or a dynamic query. A static query is the simplest of the two, all you need to do is pass a query and arguments using the db_query function. Most of your queries should be a static query because they are the fastest and the simplest.

Dynamic queries on the other hand allow other modules to modify a query by implementing the hook_query_alteror hook_query_TAG_alter hook. But beware dynamic queries are slower than static queries.

Here is what a static query looks like:

  $result = db_query("SELECT nid, title FROM {node} WHERE type = :type", array(
    ':type' => 'article',
  ));

Here is the same query written as a dynamic query:

  $query = db_select('node', 'n');
  $query
      ->fields('n', array('nid', 'title'))
      ->condition('n.type', 'article', '=');
  $result = $query->execute();

How to Use dpq()

Writing and debugging a dynamic query can be tough because you can't easily see the generated SQL string. Using the Devel module you could turn on the "Display query log" settings, but then you'll be bombarded with a lot of queries to sort through.

Note: dpq() function only works for dynamic queries that use the db_select function (SelectQuery object).

The best way to view a generated SQL string is to use the dpq function. Simply pass the $query object through to the dpq function and the SQL string will be displayed in the message region.

  $query = db_select('node', 'n');
  $query
      ->fields('n', array('nid', 'title'))
      ->condition('n.type', 'article', '=');
  $result = $query->execute();
 
  dpq($query);

Fig 1.0

In the image above, you can see the printed SQL string.

Print SQL String without Devel

If you don't want to use Devel to print the SQL string, run print_r($query->__toString()) to print the SQL string.

Here is an example:

  $query = db_select('node', 'n');
  $query
      ->fields('n', array('nid', 'title'))
      ->condition('n.type', 'article', '=');
  $result = $query->execute();
 
  print_r($query->__toString());

I still prefer to use the dpq function because the SQL string is displayed and formatted much better.

Here is an image of the same query being printed using dpq and print_r($query->__toString()).

Fig 1.1

As you can see the print_r version is just below the toolbar. If the query was larger it'll be hard to read. It is really a matter of choice if you use Devel or not.

If you have any questions, please leave a comment.

Tags: 

Ivan is the founder of Web Wash and spends most of his time consulting and writing about Drupal. He's been working with Drupal for 6 years and has successfully completed several large Drupal projects in Australia.

Connect: Twitter drupal.org LinkedIn App.net

Comments

Submitted by Alex on

Thanks for the writeup! Actually your last screenshot shows what's, in my opinion, the biggest difference: placeholder variables!

dpq() automatically replaces them so it leaves you with a query you can almost copy and paste into a mysql tool (just gotta remove those curly brackets), whereas print_r() won't do that and will let you have to figure out placeholders yourself...

Submitted by Jeroen Bobbeldijk on

To log EntityFieldQuery's, use this:

// Add this to your entityfieldquery
->addTag('debug')

Like this:

$query->entityCondition('entity_type', 'node')
->entityCondition('bundle', 'article')
->propertyCondition('status', 1)
->addTag('debug');

// Add this function to the module
function MODULE_query_alter($query) {
if ($query->hasTag('debug')) {
dpq($query);
}
}

This is because when it reaches the query_alters, it is a query object.

Submitted by kalabro on

In some cases you can't access $query object but you still can debug it:

Database::startLog('node_load_debug');
node_load(1);
dpm(Database::getLog('node_load_debug'));

Database::getLog returns log of all queries which were executed after logging with key 'node_load_debug' was started.

Submitted by Albert Volkman on

You can also just run-

print $query

__toString() is a magic method that (in this instance) turns the object into a string representation of the query.

Add new comment