tutorial - Obtener la última consulta ejecutada en PHP PDO

php pdo execute

Question

Me gustaría saber qué consulta se ejecuta con PHP PDO. Yo tengo:

<?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?

Me gustaría obtener algo como:

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

¿Es posible? Gracias

Answer 1

La mayoría de las personas crea una clase contenedora alrededor del objeto PDO para registrar las consultas a medida que se envían a la base de datos. Casi nadie usa un objeto directo de PDO ya que puede agregar métodos adicionales de ayuda envolviendo o extendiendo 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;
}

Answer 2

<?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() );