在重复密钥更新中插入PDO [英] PDO Insert on Duplicate Key Update

问题描述

发布此问题后 MySQL更新或插入或终止查询我已经更改为使用PDO，但是使用重复键更新短语时出现了一些问题.

After posting this question MySQL update or insert or die query I've change to using PDO but I'm having some issues using the on duplicate key update phrase.

这是我的数组数据的一个例子

Here's an example of my array data

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个字段，但是我不想浪费显示这些字段的空间.我目前正在尝试通过以下代码将数据库插入/更新到我的数据库中

In reality there are 113 fields but I didn't want to waste the space showing them all here. I'm currently trying to INSERT/UPDATE into my database via the following code

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吗?

I'm getting the Invalid parameter number: parameter was not defined error message. I'm pretty sure my issues lies in ON DUPLICATE KEY UPDATE :fieldlist'); but I've made so many different attempts and none of them have worked. Should I be using ON DUPLICATE KEY UPDATE anymore at all?

此外，我是:和::语法的新手，:name是否表示它是类似于$name的命名变量，而PDOStatement::bindValue是否类似于PDOStatement->bindValue?

Also, I'm new to the : and :: syntax, does :name mean it's a named variable kind of like $name and does PDOStatement::bindValue kind of like PDOStatement->bindValue?

为响应下面的前两个注释，我已经更新了代码(但仍然无济于事，debugDumpParams说我没有参数).另外，为什么当$array_of_parameters成为与$faculty完全相同的数组时创建$array_of_parameters?

In response to the first two comments below I've updated the code thusly (but still to no avail, the debugDumpParams says I have no params). Also, why create the $array_of_parameters when it becomes the exact same array as $faculty to begin with?

  //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;

推荐答案

您试图做的是动态构建将被参数化的SQL字符串. :paramname参数应该是映射到列值，where子句参数等的单个值.相反，您已经使用$fields[] = sprintf("%s = :%s", $key, $key);创建了一个:paramname字段的字符串，以便插入查询.这只是在参数化语句中不起作用.

What you have attempted to do is to dynamically build a SQL string that will become parameterized. The :paramname parameters are expected to be single values mapped to column values, where clause parameters, etc. Instead you have used $fields[] = sprintf("%s = :%s", $key, $key); to create a string of :paramname fields in order to plug into the query. This just won't work in a parameterized statement.

除了执行ON DUPLICATE KEY UPDATE :fieldlist之外，还应该在将整个sql字符串传递给prepare()之前构建整个sql字符串.

Rather than doing ON DUPLICATE KEY UPDATE :fieldlist, you should build the whole sql string before passing it into prepare().

然后，可以使用execute()的替代语法来传递期望的参数值数组，而不是单独使用bindParam()方法来绑定每个对象.它们需要以正确的顺序排列，或者具有与SQL中的:param参数相同名称的数组键. 有关详细信息和示例，请参阅文档.

Then rather than use the bindParam() method to bind each one individually, you can use an alternate syntax to execute() to pass in an array of expected parametric values. They need to be in the correct order, or have array keys the same names as the :param parameters in your SQL. See the docs for more info and examples.

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

编辑，要在UPDATE语句中正确使用参数，请执行以下操作:

EDIT To properly use parameters in your UPDATE statement, do it like this:

// 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);

这篇关于在重复密钥更新中插入PDO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！