Drupal Technical

[Drupal 7] How to print an SQL query with arguments while using db_select in Drupal 7?

Krishna.rp | | 2 min read

Drupal 7 supplies developers with a platform neutral format for generating database queries. If you are a Drupal developer, you might need to see the actual SQL query that is generated by Drupal for debugging and testing purposes. If you would like to know how to print an SQL query with arguments while using db_select in Drupal 7 then read on to find out the solution.

Usually we use dpm() for printing strings and queries in Drupal 6 and 7. However if we need to execute the query for debugging, dpm() will print only the object which is not that useful. dpq() is used in such a situation where it will print the query string with actual arguments. The dpq() function is available in the Drupal devel module.

Function dpq()

dpq($query, $return = FALSE, $name = NULL)

Function parameters

  • $query: An object that implements the SelectQueryInterface .
  • $return: Whether to return the string. Default is FALSE.
  • $name: Optional name for identifying the output.
  • return value: The $query object, or the query string if $return was TRUE.

Function definition

 function dpq($query, $return = FALSE, $name = NULL) {
  if (user_access('access devel information')) {
    $query->preExecute();
    $sql = (string) $query;
    $quoted = array();
    $connection = Database::getConnection();
    foreach ((array) $query->arguments() as $key => $val) {
      $quoted[$key] = $connection->quote($val);
    }
    $sql = strtr($sql, $quoted);
    if ($return) {
      return $sql;
    }
    dpm($sql, $name);
  }
  return ($return ? NULL : $query);
}

Example

$query = db_select('uc_zones', 'uz');
$query->join('uc_countries', 'uc', 'uz.zone_country_id = uc.country_id');
$query->fields('uz', array('zone_code', 'zone_name'));
$query->fields('uc', array('country_name'));
$query->condition('zone_country_id', array(840, 124), 'IN');
$query->orderBy('country_id', 'DESC');
$query_result = $query->execute();

In this case if I want to see the query with the actual values then usind dpq is the only way. Here is the dpq output of above query. 

SELECT uz.zone_code AS zone_code, uz.zone_name AS zone_name, uc.country_name AS country_name
FROM {uc_zones} uz
INNER JOIN {uc_countries} uc ON uz.zone_country_id = uc.country_id
WHERE (zone_country_id IN ('840', '124'))
ORDER BY country_id DESC

Hope this article was helpful. Remember to post your queries as comments.

Reference : http://api.drupal.org/api/devel/devel.module/function/dpq/7

 

 

Related Topics