PDO Prepares Statements for INSERT and ON DUPLICATE KEY UPDATE with Named Placeholders

Question

I would like to switch the prepared PDO INSERT and UPDATE statements to INSERT and ON DUPLICATE KEY UPDATE, since I think it will be much more efficient than what I am doing now, but it is difficult for me to understand the correct syntax for use with named placeholders and bindParam.

I found several similar questions on SO, but I am new to PDO and could not successfully adapt the code for my criteria. This is what I tried, but it does not work (it does not insert and does not 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(); } 

This is a simplified version of my code (I have several requests, and each request has from 20 to 50 fields). I am currently updating and checking to see if the number of rows updated is greater than 0, and if not, the insert starts, and each of these requests has its own set of bindParam statements.

Answer 1

Your ON DUPLICATE KEY syntax is incorrect.

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

You do not need to specify the table name or SET in the ON DUPLICATE KEY , and you do not need the WHERE (it always updates the record with a duplicate key).

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

You also had a PHP syntax error: you split the request into two lines.

UPDATE:

To link several parameters:

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

Then call it:

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

Answer 2

IMHO below is the correct answer for everyone who meets this again.
Note: this statement assumes that user_id is the KEY in the table.

The STATEMENT was indeed incorrect, but the accepted answer was not entirely correct.

If you insert and update using the same values ​​(and do not update them with different values), this is a fix for the request pseudo-code:

 try { //optional if your DB driver supports transactions $conn->beginTransaction(); $stmt = $conn->prepare('INSERT INTO customer_info (user_id, fname, lname) ' . 'VALUES(:user_id, :fname, :lname)' . 'ON DUPLICATE KEY UPDATE fname=VALUES(fname), lname=VALUES(lname)'); $stmt->bindParam(':user_id', $user_id); $stmt->bindParam(':fname', $_POST['fname'], PDO::PARAM_STR); $stmt->bindParam(':lname', $_POST['lname'], PDO::PARAM_STR); $stmt->execute(); //again optional if on MyIASM or DB that doesn't support transactions $conn->commit(); } catch (PDOException $e) { //optional as above: $conn->rollback(); //handle your exception here $e->getMessage() or something }