問題描述

我想將 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 準備語句的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！