php - SQLSTATE[HY093]: number of bound variables does not match number of tokens ON DUPLICATE KEY UPDATE with composite KEY

I have read a lot of post with a similar issue and am still stumped..

The table has a composite key idx_aq_contract_estimate (vendorId, projectId).

I have 9 placeholders, 9 values in the $values array, 9 keys in the array_map are mapped to ? for the ON DUPLICATE KEY UPDATE array.

 $connection = get_connection();

    if(!empty($values)  && !empty($table)) {

        $columnString = implode(', ', array_keys($values));
        $arrayOnDuplicateKeyVal = implode(', ',array_map('getKeyValue',array_keys($values, )));
        $valueString = implode(',', array_fill(0, count($values), '?'));

        $statement = $connection->prepare("INSERT INTO $table ({$columnString}) VALUES ({$valueString}) ON DUPLICATE KEY UPDATE $arrayOnDuplicateKeyVal");
        //print_r ($statement) at this point:
        //INSERT INTO `aq_contract_estimate` (vendorId, name, projectId, exportId, total_freight, total_taxable, total_nontaxable, total_credit_taxable, total_credit_nontaxable) VALUES (?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE vendorId = ?, name = ?, projectId = ?, exportId = ?, total_freight = ?, total_taxable = ?, total_nontaxable = ?, total_credit_taxable = ?, total_credit_nontaxable = ?
)
        $statement->execute(array_values($values));

        print $statement->errorCode();
        //prints: Invalid parameter number: number of bound variables does not match number of tokens upon $statement->execute()
 

        print_r($statement->errorInfo());

        $statement->closeCursor();

If I remove "ON DUPLICATE KEY UPDATE $arrayOnDuplicateKeyVal")" the statement executes fine, so I am confident my field names are correctly spelled, etc., but I need to handle duplicate keys.

What is incorrect about this approach using tokens vs. named place holders??

Solution:

You can rewrite your query as

INSERT INTO `aq_contract_estimate` (vendorId, name, projectId, exportId, total_freight, total_taxable, total_nontaxable, total_credit_taxable, total_credit_nontaxable) 
VALUES (?,?,?,?,?,?,?,?,?) 
ON DUPLICATE KEY UPDATE vendorId = VALUES(vendorId), name = VALUES(name), projectId = VALUES(projectId)
, exportId = VALUES(exportId), total_freight = VALUES(total_freight), total_taxable = VALUES(total_taxable), total_nontaxable = VALUES(total_nontaxable), total_credit_taxable = VALUES(total_credit_taxable )
, total_credit_nontaxable = VALUES(total_credit_nontaxable)

So ar leasr you wouldn't need to double the niber of values.

but ON DUPICATE UPDATE works, when the insert encounters a unique constraint and violates it, so that column or columns you really don't need to update.