PDO插入重复键更新

Question

在发布了这个问题之后MySQL update or insert or die query，我已经改用PDO，但是在使用on duplicate key update短语时遇到了一些问题。

以下是我的数组数据示例。

array(114) {
["fname"]=>
string(6) "Bryana"
["lname"]=>
string(6) "Greene"
["m080"]=>
string(1) "c"
["t080"]=>
string(1) "-"
["w080"]=>
string(1) "-"
["r080"]=>
["notes"]=>
string(4) "yoyo"}

实际上有113个字段，但我不想浪费空间在这里展示它们。我目前正在尝试通过以下代码向我的数据库插入/更新数据

try {
    $dbh = new PDO('login info here');
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $stmt = $dbh->prepare(
        'INSERT INTO fhours ('.implode(",", array_keys($faculty)).')'.
        ' VALUES (:'.implode(",:", array_keys($faculty)).')'.
        ' ON DUPLICATE KEY UPDATE :fieldlist');

    $stmt->bindParam(':field_list', $field_list);

    foreach($faculty as $key=>$val){
        $stmt->bindParam(':'.$key, $val);
        $fields[] = sprintf("%s = :%s", $key, $key);
    }
    $field_list = join(',', $fields);
    //echo $stmt->debugDumpParams();
    $stmt->execute();
}
catch(PDOException $e){
    echo $e->getMessage();
    exit(); 
}

我收到了“无效的参数号：未定义参数”错误消息。 我相信我的问题在于ON DUPLICATE KEY UPDATE :fieldlist'，但我尝试了许多不同的方法，但都没有成功。 我还需要使用ON DUPLICATE KEY UPDATE吗？

此外，我对:name和::语法很陌生，:name是否意味着它是一种命名变量，就像$name一样，并且PDOStatement::bindValue类似于PDOStatement->bindValue？

编辑

根据下面的前两条评论，我已经更新了代码（但仍然没有成功，debugDumpParams说我没有参数）。 另外，为什么要创建$array_of_parameters，当它最初与$faculty相同？

  //grab form data
$faculty = $_POST;
$fname = $_POST['fname'];
$lname = $_POST['lname'];
//delete the submit button from array
unset($faculty['submit']);
$array_of_parameters = array();
foreach($faculty as $key=>$val){
        $array_of_parameters[$key] = $val;
        $fields[] = sprintf("%s=?", $key);
}
$field_list = join(',', $fields);

try {
    $dbh = new PDO('mysql:host=localhost;dbname=kiosk', 'kiosk', 'K10$k');
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

    $update =   'UPDATE fhours SET '.$field_list. 'WHERE fname="'.$fname.'" AND '.
                        'lname="'.$lname.'"';
    $stmt = $dbh->prepare($update);
    //echo $stmt->debugDumpParams();
    $stmt->execute(array($array_of_parameters));

    if($stmt->rowCount() == 0){
        $insert = 'INSERT INTO fhours ('.implode(",", array_keys($faculty)).')'.
                    ' VALUES (:'.implode(",:", array_keys($faculty)).')';
        $stmt = $dbh->prepare($insert);
        $stmt->execute(array($array_of_parameters));
    }
}
catch(PDOException $e){
    echo $e->getMessage();
    exit(); 
}

$dbh=null;

- Michael

---

创建$array_of_parameters的原因是因为在第一次迭代中，您对ON DUPLICATE KEY子句和INSERT VALUES()都使用了绑定参数。所有这些参数都必须放在传递给execute()的同一个数组中。第一次使用$array_of_parameters调用execute()是在UPDATE语句中，您没有使用任何参数。此外，您将数组包装在另一个不必要的array()中。 - Michael Berkowski

你的第一个UPDATE语句进行了SQL字符串拼接，然后传递给prepare()函数，因此仍然容易受到注入攻击。请参阅我的答案编辑获取更多信息。 - Michael Berkowski

在SET子句中，逗号分隔的字段之间可能需要加上空格 - 我不确定是否可以省略空格。使用逗号和空格连接它们：$field_list = join（'，'，$fields）; - Michael Berkowski

Answer 1

冒号前缀的名称只是命名占位符。当您要绑定参数时，只需将占位符绑定到任意值即可。 ON DUPLICATE KEY UPDATE不太适用于多个DBMS，但如果您连接到兼容的数据库，则应该可以使用（因为我不认为PDO会阻止所有这些，但我可能错了）。出于可移植性的考虑，我不会使用它。您可能需要检查如何绑定字段列表，bindparam应该只执行一个参数，并且那些是列，不应像值一样被引用（bindparam会这样做）。

我通过运行最多两个查询来设计upserts：先更新，然后插入。首先更新并检查更新的行数是否大于0。如果受影响的行数为0，则运行插入操作。

仅作闲话，113个字段是非常多的字段，如果不小心可能会导致表性能下降。

- SamT

---

表格是一个星期一至星期五的时间表，从早上8点到晚上6点半，以30分钟为间隔，用于教职员工输入他们的办公时间。关于数据库设计，我也可以接受其他想法，但那是另一个话题了。 - Michael

Answer 2

您尝试动态构建一个SQL字符串，使其成为参数化。其中：paramname参数被期望是单个值映射到列值、where子句参数等，而您却使用了$fields[] = sprintf("%s = :%s", $key, $key);来创建一串：paramname字段的字符串以插入到查询中。这在参数化语句中不起作用。

您应该在将整个SQL字符串传递给prepare()之前构建它。然后，您可以使用替代execute()语法来传递预期的参数化值数组，而不是使用bindParam()方法逐个绑定每个值。它们需要按正确的顺序排序，或者具有与SQL中:param参数相同名称的数组键。有关更多信息和示例，请参见文档。

$array_of_parameters = array();
foreach($faculty as $key=>$val){
    $array_of_parameters[$key] = $val);
}
$stmt->execute($array_of_parameters);

编辑：正确使用UPDATE语句中的参数，请按照以下方式进行：

// Create your $field_list before attempting to create the SQL statement
$field_list = join(',', $fields);

$update = 'UPDATE fhours SET '.$field_list. 'WHERE fname=:fname AND lname=:lname';
// Here, echo out $update to make sure it looks correct

// Then add the fname and lname parameters onto your array of params
$array_of_parameters[] = $_POST['fname'];
$array_of_parameters[] = $_POST['lname'];

// Now that your parameters array includes all the faculty in the correct order and the fname & lname,
// you can execute it.
$stmt->prepare($update);
$stmt->execute($array_of_parameters);

- Michael Berkowski

---

Michael，非常感谢您的帮助。代码现在全部都可以正常运行了。 - Michael