IMSSP "试图绑定参数号0"在foreach循环中绑定数组值时出错 - IMSSP "Tried to bind parameter number 0" error when binding array values in foreach loop

IMSSP "Tried to bind parameter number 0" error when binding array values in foreach loop

我有一个PHP关联数组，我想在foreach循环中用PDO绑定它的值。到目前为止，我有这个:

$sqlSecondaryInsert = "INSERT INTO TCMS_Documents ";
// Table Fields
$sqlSecondaryFields = " (DocumentID, ";
$sqlSecondaryValues = "VALUES (:lastDocumentInsertID, ";
foreach ($intersectArray as $key => $value) {
    if (trim($value) != '') {
        $sqlSecondaryFields.= trim($key) . ", ";
        $sqlSecondaryValues.= ":" . trim($key) . ", ";
    }
}
$sqlSecondaryFields = rtrim($sqlSecondaryFields, ", ");
$sqlSecondaryFields.= ") ";
$sqlSecondaryValues = rtrim($sqlSecondaryValues, ", ");
$sqlSecondaryValues.= ")";
$sqlSecondaryStmt = $sqlSecondaryInsert . $sqlSecondaryFields;
$stmt2 = $connPDO->prepare($sqlSecondaryStmt);
$stmt2->bindValue(':lastDocumentInsertID', $lastDocumentInsertID);
foreach ($intersectArray as $key => $value) {
    error_log("attempting to bind " . $key . " to value " . $value);
    $stmt2->bindValue(':' . $key, $value, PDO::PARAM_STR);
}
$stmt2->execute();

虽然SQL INSERT语句正在正确生成，但在尝试执行它时，我在PHP错误日志中收到以下内容:

"IMSSP",-29,"Tried to bind parameter number 0.  SQL Server supports a maximum of 2100 parameters."

PHP日志显示了foreach循环内error_log的正确键和值，所以我不明白为什么PDO bindValues不工作。

$intersectArray的print_r示例如下:

[DocumentNumber] => 123
[ValidFromDate] => 02/09/2015
[ValidExpiryDate] => 26/09/2015

Edit:一些可能有用也可能没用的附加信息:我们使用的是SQL Server 2008。我不知道这是否相关，我不了解PDO驱动程序如何不同于SQLSRV和MySQL…

试试这个:

下面是一个例子。

function generateInsert($table, $data){
    $sql = "INSERT INTO $table (".implode(", ", $data).") VALUES (:".implode(", :", array_keys($data)).")";
    $exe = array();
    foreach($data as $k=>$v) { 
      $exe[":$k"] = $v;
    }
    return array("sql"=>$sql, "exe"=>$exe);
}
$q = generateInsert("TCMS_Documents", $intersectArray);
$sql = $q['sql'];
$exe = $q['exe'];
$stmt = $connPDO->prepare($sql);
$stmt->execute($exe);

确保您将lastDocumentInsertID列添加到$intersectArray数组中。