PDO为具有命名占位符的INSERT和ON DUPLICATE KEY UPDATE准备的语句 [英] 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，如果不行，则运行Insert，并且每个查询都有自己的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);      

您不需要将表名或SET放在ON DUPLICATE KEY子句中，也不需要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);

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