Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PDO prepared statements for INSERT and ON DUPLICATE KEY UPDATE with named placeholders

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.

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();
}

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.

like image 470
Chaya Cooper Avatar asked Dec 30 '12 07:12

Chaya Cooper


People also ask

What is PDO prepared statements?

In layman's terms, PDO prepared statements work like this: Prepare an SQL query with empty values as placeholders with either a question mark or a variable name with a colon preceding it for each value. Bind values or variables to the placeholders. Execute query simultaneously.

How PDO works?

PDO—PHP Data Objects—are a database access layer providing a uniform method of access to multiple databases. It doesn't account for database-specific syntax, but can allow for the process of switching databases and platforms to be fairly painless, simply by switching the connection string in many instances.


2 Answers

Your ON DUPLICATE KEY syntax is not correct.

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

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

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

UPDATE:

To bind multiple 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);
like image 53
Barmar Avatar answered Sep 27 '22 16:09

Barmar


IMHO below is the right answer for anyone coming across this again.
Note: this statement assumes user_id is a KEY in the table.

The STATEMENT indeed was wrong, but the accepted answer was not completely correct.

If you're inserting and updating using the same values (and not updating with different values), this is the query pseudo-code corrected:

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
}
like image 36
Ligemer Avatar answered Sep 27 '22 17:09

Ligemer