RedGiant RedGiant - 5 months ago 74
PHP Question

Binding values with INSERT INTO ON DUPLICATE KEY in PDO

I have run into a problem with binding values with a

INSERT INTO ON DUPLICATE KEY
query in PDO.
The error I'm getting is
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
.

The buildup of the SQL seems to be correct:

INSERT INTO `mytable` (user_name,user_id)
VALUES (?,?) ON DUPLICATE KEY UPDATE user_name = ?,user_id = ?


Am I right in thinking that it needs binding the same values again to the parameters in the
foreach
loop for the
ON DUPLICATE KEY UPDATE
clause? Can anyone show me how to do that?

$update_fields .= $comma.$filter. " = ?";
$filters = array("user_name"=>"User Name","user_id"=>"User ID");
$field = "";
$comma = "";

foreach ($filters as $filter => $title) {
$filterVar = "post_".$filter;
$$filterVar = (isset($_POST["$filterVar"]) ? $_POST["$filterVar"] : "");

if ($$filterVar) {
$fields .= $comma.$filter;
$values .= $comma.$$filterVar;
$update_fields .= $comma.$filter. " = ?";
$comma = ",";
}
}

$values_arr = explode(',',$values);
$in = str_repeat('?,', count($values_arr) - 1) . '?';

$sql .= "INSERT INTO `mytable` ($fields) VALUES ($in)";
$sql .= "ON DUPLICATE KEY UPDATE $update_fields ";

$users = $dbh->prepare($sql);
print $sql;
$i = 1;

foreach ($values_arr as $value) { // binding values
$users->bindValue($i++, $value);
}

$users->execute($values_arr);

Answer

You are right that it will need to bind the same values for both parts. But I think if you are going to pass a values array to execute, you do not need to bind values beforehand, so you should be able to skip the foreach...bindValue part. This is like Example 3 in the PHP docs on PDOStatement::execute. However, since you have twice as many ? placeholders because of the ON DUPLICATE KEY UPDATE part, your values array will need to be doubled as well. I think this should work.

$double_values = array_merge($values_arr, $values_arr);
$users->execute($double_values);