带有命名占位符的 INSERT 和 ON DUPLICATE KEY UPDATE 的 PDO 准备语句 [英] PDO prepared statements for INSERT and ON DUPLICATE KEY UPDATE with named placeholders

问题描述

我想将 PDO INSERT 和 UPDATE 准备好的语句切换为 INSERT 和 ON DUPLICATE KEY UPDATE，因为我认为它会比我目前正在做的更有效率，但我无法弄清楚与命名占位符和 bindParam 一起使用的正确语法.

I'd like to switch PDO INSERT and UPDATE prepared statements to INSERT and ON DUPLICATE KEY UPDATE since I think it'll be a lot more efficient than what I'm currently doing, but I'm having trouble figuring out the correct syntax to use with named placeholders and bindParam.

我在 SO 上发现了几个类似的问题，但我是 PDO 的新手，无法成功地根据我的标准调整代码.这是我尝试过的，但它不起作用(它不插入或更新):

I found several similar question on SO, but I'm new to PDO and couldn't successfully adapt the code for my criteria. This is what I've tried, but it doesn't work (it doesn't insert or update):

try { 
  $stmt = $conn->prepare('INSERT INTO customer_info (user_id, fname, lname) VALUES(:user_id, :fname, :lname)'          
 'ON DUPLICATE KEY UPDATE customer_info SET fname= :fname, 
                                            lname= :lname   
                                            WHERE user_id = :user_id'); 
  $stmt->bindParam(':user_id', $user_id);  
  $stmt->bindParam(':fname', $_POST['fname'], PDO::PARAM_STR);
  $stmt->bindParam(':lname', $_POST['lname'], PDO::PARAM_STR);      
  $stmt->execute();
}

这是我的代码的简化版本(我有几个查询，每个查询有 20 - 50 个字段).我目前正在首先更新并检查更新的行数是否大于 0，如果不是则运行插入，并且每个查询都有自己的一组 bindParam 语句.

This is a simplified version of my code (I have several queries, and each query has between 20 - 50 fields). I'm currently updating first and checking if the number of rows updated is greater than 0 and if not then running the Insert, and each of those queries has it's own set of bindParam statements.

推荐答案

您的 ON DUPLICATE KEY 语法不正确.

$stmt = $conn->prepare('INSERT INTO customer_info (user_id, fname, lname) VALUES(:user_id, :fname, :lname)
    ON DUPLICATE KEY UPDATE fname= :fname2, lname= :lname2');

$stmt->bindParam(':user_id', $user_id);  
$stmt->bindParam(':fname', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname', $_POST['lname'], PDO::PARAM_STR);      
$stmt->bindParam(':fname2', $_POST['fname'], PDO::PARAM_STR);
$stmt->bindParam(':lname2', $_POST['lname'], PDO::PARAM_STR);      

ON DUPLICATE KEY 子句中不需要放表名或SET，也不需要WHERE> 子句(它总是用重复的键更新记录).

You don't need to put the table name or SET in the ON DUPLICATE KEY clause, and you don't need a WHERE clause (it always updates the record with the duplicate key).

参见 http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

您还有一个 PHP 语法错误:您将查询拆分为两个字符串.

You also had a PHP syntax error: you split the query up into two strings.

更新:

绑定多个参数:

function bindMultiple($stmt, $params, &$variable, $type) {
  foreach ($params as $param) {
    $stmt->bindParam($param, $variable, $type);
  }
}

然后调用它:

bindMultiple($stmt, array(':fname', ':fname2'), $_POST['fname'], PDO::PARAM_STR);

这篇关于带有命名占位符的 INSERT 和 ON DUPLICATE KEY UPDATE 的 PDO 准备语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！