Retrieve (or simulate) full query from PDO prepared statement

Viewed 78 times

I stumbled upon this question from two .

Is there a way to get the raw SQL string executed when calling PDOStatement::execute() on a prepared statement? For debugging purposes this would be extremely useful.

The winning answer states that

[...] You can also get what you want if you set the PDO attribute PDO::ATTR_EMULATE_PREPARES. In this mode, PDO interpolate parameters into the SQL query and sends the whole query when you execute().

But it doesn't mention how to get the resulting query string. I know it's a bad idea performance wise but that doesn't bother me in debug mode. Does anybody know how to do this?

PS If there is some way I could have reopened / drawn attention to the original two year old topic instead of opening a new one, please let me know.

Answers

I believe this is mentioned in the original question that was reference in this one. However there is actually supposed to be a method for retrieving this data.

PDOStatement::debugDumpParams

However it isn't currently working as documented. There is a bug report and patch submitted for it here http://bugs.php.net/bug.php?id=52384 in case anyone is interested in voting on it. Until it's fixed it seems like you are left to use query logging or setting a custom statement class using the PDO::ATTR_STATEMENT_CLASS attribute.

Tuesday, December 27, 2022

tysc2002916zhangqiling-741d67556b79

Yes, bindParam binds a parameter to a variable name (reference), not a value, as the manual says.

However, there's a simpler syntax for your situation. PDOStatement::execute can take an array of values.

public function insert($table, $cols, $values){

    $placeholder = array();
    for ($i = 0; i < count($values); $i++)
      $placeholder[] = '?';

    $sql = 'INSERT INTO '. $table . ' (`' . implode("`, `", $cols) . '`) ';
    $sql.= 'VALUES (' . implode(", ", $placeholder) . ')';

    $stmt = $this->dbh->prepare($sql);
    $stmt->execute($values);

}

Wednesday, December 14, 2022

ben_scheirman

Yes, you can reuse the same prepared statement, but not how you have it in the question. What you are trying to do is essentially the same as doing this:

for ($i=0; $i<$some_number; $i++) {
    echo $i."n";
    for ($i=0; $i<$some_number; $i++) {
        // do something
    }
}

The the second for loop moves the same pointer as the original one, so therefore the output from the above would simply be "0" indicating that the original for loop only happened once.

So in order to get around this, you will need to store the results of the first execute into an array and then iterate over it. That way you won't have to worry about any pointers

$SQL = $dbh->prepare("SELECT * FROM user WHERE id=? AND users=?");
$SQL->execute(array($id,$userid));
$checks = $SQL->fetchAll();
foreach ($checks as $check) {
    $SQL->execute(array($id2,$userid2));
    while ($check2 = $SQL->fetchObject(){
        //while loops for second execution
    }
}

This way, you are using exactly the same prepared statement (which is good) and the original $check variable is available to be used in the while loop.

However, with all that said, I have a strong hunch that you can probably get everything into one single SQL query without the need for looping over it like this.

Monday, October 24, 2022

david_zorychta

I don't know much about PDO, but my feeling is there is something wrong with the way you bind the parameters. However, the easiest way to tell for sure is to see the actual query.

According to the docs, you should be able to see the generated query as it went to SQL in $stmt->queryString. It's not possible to see right now because you are binding the parameters to the statement after you are outputting $stmt.

Do a print_r() after you bind the parameters (or maybe even after execution of the query, I don't know). You should get the real query string, and get to the bottom of the problem.

Monday, November 7, 2022

kalit

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

pdo

prepared-statement

Retrieve (or simulate) full query from PDO prepared statement

Answers

php,pdo,prepared-statement