How can I view a prepared PDO SQL Statement

Question

Possible Duplicate:
PDO Prepared Statements

I'm sure the answer to this is very simple, but I don't seem to be able to find it.

I'm using PDO (PHP Data Objects) to run a query against a MySQL database, and would find it useful to display the prepared query before it is executed against the DB.

Is there any way of doing this? For example:

$query = 'SELECT Id, Name, Comment FROM Users WHERE Id = :id';
$pdoStatement = $db->prepare($query);
$pdoStatement->bindValue(':id', $id);

// How can I view the actual statement that will be executed, showing the real
// value that will be used in place of ':id'

$pdoStatement->execute();

It is discussed here http://stackoverflow.com/questions/2411182/how-to-debug-pdo-database-queries and http://stackoverflow.com/questions/6961897/php-pdo-how-can-i-display-the-query-pdo-just-prepared — Bob, Jun 22 '12 at 12:43
I obviously didn't search too well, given the speed at which you both found the other posts. — Bryan, Jun 22 '12 at 12:48
@Bryan It was the first one in the related questions panel on the right hand side! — lonesomeday, Jun 22 '12 at 12:49
@lonesomeday I performed a few searches on Google, which didn't yield anything useful, then just posted the question without checking the related questions. My bad. Thanks again though. — Bryan, Jun 22 '12 at 12:51
Thanks @vishal, wasn't aware of that, seems like a good solution. — Bryan, Jan 06 '14 at 19:24

score 3 · Answer 1 · answered Jun 22 '12 at 12:43

3

You can't get the query which is sent to server because PDO doesn't work this way.

It sends the $query seperately and $id seperately to the server-database which are executed after joining by database.

answered Jun 22 '12 at 12:43

score 3 · Answer 2 · answered Jun 22 '12 at 12:43

A frequent practice is to print the query (which has placeholders in it) alongside the bound values. When using an array of the style :placeholder => value you can just var_dump, print_r or var_export the array.

This is done in Magento SQL debugging for example.

The "final" query doesn't exist as a string unless the PDO driver doesn't support prepared statements and it's simulating them.

In essence you can think of prepared statements as if they were a stored function or a stored procedure. You create it once and execute it multiple times with multiple parameters.

score 3 · Answer 3 · edited May 23 '17 at 12:04

use 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
 */
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;
}

source: View and debug prepared PDO query without looking at MySQL logs

How can I view a prepared PDO SQL Statement

3 Answers3