View and debug prepared PDO query without looking at MySQL logs

Question

I want to see what PDO is preparing without looking into the MySQL logs. Basically the final query it has built right before it executes the query.

Is there a way to do this?

Answer 1

There is no built-in way to do it. bigwebguy created a function to do it in one of his answers:

/**
 * Replaces any parameter placeholders in a query with the value of that
 * parameter. Useful for debugging. Assumes anonymous parameters from 
 * $params are are in the same order as specified in $query
 *
 * @param string $query The sql query with parameter placeholders
 * @param array $params The array of substitution parameters
 * @return string The interpolated query
 */
public static function interpolateQuery($query, $params) {
    $keys = array();

    # build a regular expression for each parameter
    foreach ($params as $key => $value) {
        if (is_string($key)) {
            $keys[] = '/:'.$key.'/';
        } else {
            $keys[] = '/[?]/';
        }
    }

    $query = preg_replace($keys, $params, $query, 1, $count);

    #trigger_error('replaced '.$count.' keys');

    return $query;
}

Answer 2

This is just a derivation of @Maerlyn code above which accept non-asociative arrays for params like and where if the key starts already with ':' don't add it.

/**
 * Replaces any parameter placeholders in a query with the value of that
 * parameter. Useful for debugging. Assumes anonymous parameters from 
 * $params are are in the same order as specified in $query
 *
 * @param string $query The sql query with parameter placeholders
 * @param array $params The array of substitution parameters
 * @return string The interpolated query
 * 
 * @author maerlyn https://stackoverflow.com/users/308825/maerlyn
 */
function interpolateQuery($query, $params) {
    $keys = array();

    # build a regular expression for each parameter

    if (!isAssoc($params)){
        $_params = [];  // associative array
        foreach($params as $param){
            $key = $param[0];
            $value = $param[1];
            // $type = $param[2];
            $_params[$key] = $value;
        }
        $params  = $_params;
    }       

    foreach ($params as $key => $value) {
        if (is_string($key)) {
            $keys[] = '/'.((substr($key,0,1)==':') ? '' : ':').$key.'/';
        } else {
            $keys[] = '/[?]/';
        }
    }

    $query = preg_replace($keys, $params, $query, 1, $count);

    #trigger_error('replaced '.$count.' keys');

    return $query;
}