Asked  7 Months ago    Answers:  5   Viewed   38 times

I stumbled upon this question from two years ago.

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

56

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.

Wednesday, March 31, 2021
 
EurekA
answered 7 Months ago
24

Your ON DUPLICATE KEY syntax is not correct.

$stmt = $conn->prepare('INSERT INTO customer_info (user_id, fname, lname) VALUES(:user_id, :fname, :lname)
    ON DUPLICATE KEY UPDATE fname= :fname2, lname= :lname2');

$stmt->bindParam(':user_id', $user_id);  
$stmt->bindParam(':fname', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname', $_POST['lname'], PDO::PARAM_STR);      
$stmt->bindParam(':fname2', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname2', $_POST['lname'], PDO::PARAM_STR);      

You don't need to put the table name or SET in the ON DUPLICATE KEY clause, and you don't need a WHERE clause (it always updates the record with the duplicate key).

See http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

You also had a PHP syntax error: you split the query up into two strings.

UPDATE:

To bind multiple parameters:

function bindMultiple($stmt, $params, &$variable, $type) {
  foreach ($params as $param) {
    $stmt->bindParam($param, $variable, $type);
  }
}

Then call it:

bindMultiple($stmt, array(':fname', ':fname2'), $_POST['fname'], PDO::PARAM_STR);
Wednesday, March 31, 2021
 
Gil
answered 7 Months ago
Gil
39

I can find nothing clear in the manual, but looking at the User Contributed Notes, the use of parameters is intended for actual values only, not table names, field names etc.

Normal string concatenation should (and can) be used.

$tablename = "tablename";
$stmt = $dbh->prepare("CREATE TABLE `$tablename` (id foo, int bar,...)");
Wednesday, March 31, 2021
 
kmunky
answered 7 Months ago
67

It can work with bind param too in following way:

$name = "%$name%";
$query = $dbh->prepare("SELECT * FROM `gc_users` WHERE `name` like :name");
$query->bindParam(':name', $name);
$query->execute();
Wednesday, June 9, 2021
 
erotsppa
answered 5 Months ago
78

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.

Friday, July 30, 2021
 
user729076
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :