Get Last Executed Query in PHP Pdo

Get Last Executed Query in PHP PDO

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

In PHP with PDO, how to check the final SQL parametrized query?

So I think I'll finally answer my own question in order to have a full solution for the record. But have to thank Ben James and Kailash Badu which provided the clues for this.

Short Answer

As mentioned by Ben James: NO.

The full SQL query does not exist on the PHP side, because the query-with-tokens and the parameters are sent separately to the database.
Only on the database side the full query exists.

Even trying to create a function to replace tokens on the PHP side would not guarantee the replacement process is the same as the SQL one (tricky stuff like token-type, bindValue vs bindParam, ...)

Workaround

This is where I elaborate on Kailash Badu's answer.
By logging all SQL queries, we can see what is really run on the server.
With mySQL, this can be done by updating the my.cnf (or my.ini in my case with Wamp server), and adding a line like:

log=[REPLACE_BY_PATH]/[REPLACE_BY_FILE_NAME]

Just do not run this in production!!!

Get query back from PDO prepared statement

Try $statement->queryString.

Get last executed query in MySQL with PHP/CodeIgniter

Use:

$this->db->last_query();

Returns the last query that was run (the query string, not the result). Example:
$str = $this->db->last_query();

// Produces: SELECT * FROM sometable.... 

example taken from the manual on query helper functions

How to debug PDO database queries?

Looking in the database log

Although Pascal MARTIN is correct that PDO doesn't send the complete query to the database all at once, ryeguy's suggestion to use the DB's logging function actually allowed me to see the complete query as assembled and executed by the database.

Here's how:
(These instructions are for MySQL on a Windows machine - your mileage may vary)

• In my.ini, under the [mysqld] section, add a log command, like log="C:\Program Files\MySQL\MySQL Server 5.1\data\mysql.log"
• Restart MySQL.
• It will start logging every query in that file.

That file will grow quickly, so be sure to delete it and turn off logging when you're done testing.

How to tell when query executed successfully in PHP PDO?

The execute returns true on success and false on failure.

From Docs:

Returns TRUE on success or FALSE on failure.

---

So you can make sure query ran successfully like:

if ($STH->execute($params))
{
  // success
}
else
{
  // failure
}

How to check last executed query in Opencart3

... just like code Igniter has an option $this->db->last_query() , don't we have something similar in Opencart3 ?

No. Opencart is not a framework (like CodeIgniter) and doesn't offer as robust a library of functions.

If this is the original Db object, are those really all the functions we have for $this->db object?

Yes.

I need to check what query is executed in $this->config->get

That doesn't make any sense. $this->config->get() simply gets the value of an array key of the $data property of the config class. All of those properties are loaded on site initialization. Most of them (not all) come from this query in controller/startup/startup.php:

SELECT * FROM `" . DB_PREFIX . "setting` WHERE store_id = '0' OR store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY store_id ASC

If you want to know where that specific value came from it's most likely going to be in the setting table where key = 'visit_module_enabled'.

If you still want to create a last_query() method, it would be very easy, but based on your question I don't think it's going to get you what you want.

To create a last_query() method, you can modify the db class like this:

private $last_query;

public function query($sql) {
    $this->last_query = $sql;
    return $this->adaptor->query($sql);
}

public function last_query() {
    return $this->last_query;
}

PHP PDO MySQL Correct way to check if an update query succeeded when no rows are affected

The execute() method will either throw an exception or return FALSE (depending on the error mode you have set for the database connection) when the execution of a SQL statement fails.

If we set the error mode to throw an exception, prior to executing a statement, (usually immediately after establishing a database connection), like this

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

Then we can use a try/catch block to handle an exception thrown by a SQL statement. Something like this:

try {
  $query->execute(...);
  echo 'Update succeeded';

} catch(PDOException $e) {
  echo 'Update failed!';
  echo 'Error: ' . $e->getMessage();
}

Information about error mode settings and handling is available in the documentation here: http://php.net/manual/en/pdo.error-handling.php

---

Or, if PDO isn't set to throw an exception, we can use a simple if test. (The execute() method will return FALSE if the the statement fails.)

if ($query->execute(...)) {
   echo 'Update succeeded';

} else {
   echo 'Update failed!';

}

For more precise control with the different types of failure, we can use the errorCode() method to retrieve the SQLSTATE associated with the last operation on the statement handle, and we can perform conditional tests on the returned value. http://php.net/manual/en/pdostatement.errorcode.php

My query doesn't work if the last query has a comment on it.

The culprit is not the comment itself. It's the combination of having a ; statement delimiter, a comment and two different statements. Here's a simpler reproduce code:

$stmt = $con->prepare('SELECT CURRENT_DATE; #');
$stmt->execute();
var_dump($stmt->fetchAll());

$stmt2 = $con->prepare('SELECT CURRENT_TIME');
$stmt2->execute();
var_dump($stmt2->fetchAll());

array(1) {
  [0]=>
  array(2) {
    ["CURRENT_DATE"]=>
    string(10) "2018-11-13"
    [0]=>
    string(10) "2018-11-13"
  }
}
array(0) {
}

What's probably happening is that PDO believes you're issuing multiple statements thus expects multiple result-sets. If you overwrite the statement it seems to clear stuff automatically but if you create a new variable it seems it's still expecting the first statement to complete.

If you call PDOStatement::nextRowset() everything works again:

$stmt = $con->prepare('SELECT CURRENT_DATE; #');
$stmt->execute();
var_dump($stmt->fetchAll());
$stmt->nextRowset(); // <--- Retrieve the "phantom" result-set

$stmt2 = $con->prepare('SELECT CURRENT_TIME');
$stmt2->execute();
var_dump($stmt2->fetchAll());

Alternatively, you can set PDO::ATTR_EMULATE_PREPARES => false so PDO will not parse SQL and let the job to MySQL server.

---

Related Topics