Get Last Executed Query in PHP PDO

Question

I would like to know what query is executed using PHP PDO. I have:

<?php

try {  
  $DBH = new PDO("mysql:host=localhost;dbname=mytable", 'myuser', 'mypass');  
}  
catch(PDOException $e) {  
    echo $e->getMessage();  
}  

$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );  
$DBH->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  

$STH = $DBH->("INSERT INTO mytable (column1, column2, column3 /* etc...*/) value (:column1, :column2, :column3 /* etc...*/)"); 
$STH->bindParam(':column1', $column1);  
$STH->bindParam(':column2', $column2);  
$STH->bindParam(':column3', $column3);  
 /* etc...*/

$STH->execute();  

// what is my query?

I would like to get something like:

INSERT INTO mytable (column1, column2, column3) value ('my first column', 32, 'some text')

Is it possible? Thanks

Answer 1

<?php  class MyPDOStatement extends PDOStatement {   protected $_debugValues = null;    protected function __construct()   {     // need this empty construct()!   }    public function execute($values=array())   {     $this->_debugValues = $values;     try {       $t = parent::execute($values);       // maybe do some logging here?     } catch (PDOException $e) {       // maybe do some logging here?       throw $e;     }      return $t;   }    public function _debugQuery($replaced=true)   {     $q = $this->queryString;      if (!$replaced) {       return $q;     }      return preg_replace_callback('/:([0-9a-z_]+)/i', array($this, '_debugReplace'), $q);   }    protected function _debugReplace($m)   {     $v = $this->_debugValues[$m[1]];     if ($v === null) {       return "NULL";     }     if (!is_numeric($v)) {       $v = str_replace("'", "''", $v);     }      return "'". $v ."'";   } }  // have a look at http://www.php.net/manual/en/pdo.constants.php $options = array(   PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,   PDO::ATTR_STATEMENT_CLASS => array('MyPDOStatement', array()), );  // create PDO with custom PDOStatement class $pdo = new PDO($dsn, $username, $password, $options);  // prepare a query $query = $pdo->prepare("INSERT INTO mytable (column1, column2, column3)   VALUES (:col1, :col2, :col3)");  // execute the prepared statement $query->execute(array(   'col1' => "hello world",   'col2' => 47.11,   'col3' => null, ));  // output the query and the query with values inserted var_dump( $query->queryString, $query->_debugQuery() ); 

Answer 2

Most people create a wrapper class around the PDO object to record the queries as they are sent to the database. Hardly anyone uses a direct PDO object since you can add extra helper methods by wrapping, or extending PDO.

/**
 * Run a SQL query and return the statement object
 *
 * @param string $sql query to run
 * @param array $params the prepared query params
 * @return PDOStatement
 */
public function query($sql, array $params = NULL)
{
    $statement = $this->pdo->prepare($sql);

    $statement->execute($params);

    // Save query results by database type
    self::$queries[] = $sql;

    return $statement;
}