How can I use ON DUPLICATE KEY UPDATE in PDO with mysql?

Question

DETAILS

I am doing a single insert for the expiry of a new or renewed licence. The time period for the expiry is 2 years from the insertion date. If a duplicate is detected, the entry will be updated such that the expiry equals the remaining expiry plus 2 years.

Regarding duplicates, in the example below there should only be one row containing user_id =55 and licence=commercial.

TABLE: licence_expiry

--------------------------------------------------------
|   user_id   |   licence   |           expiry         |  
--------------------------------------------------------
|     55      |  commercial |     2013-07-04 05:13:48  |  
---------------------------------------------------------

user_id (int11), licence (varchan50), expiry (DATETIME)

I think in mysql you would write it something like this (Please note that I haven't checked whether the code works in mysql. )

INSERT INTO `licence_expiry`
(`user_id`, `licence`, `expiry`)
VALUES
(55, commercial, NOW()+ INTERVAL 2 YEAR)
ON DUPLICATE KEY UPDATE
`expiry` = `expiry` + INTERVAL 2 YEAR

QUESTION: How can I do this with PDO? I've written a rough outline of what I think I will use, but I'm not sure what to write for the expiry value for the ON DUPLICATE KEY UPDATE.

$sql = "INSERT INTO $table (user_id, licence, expiry)
                        VALUES (
                        :user_id,  
                        :licence,
                        :expiry)
    ON DUPLICATE KEY UPDATE expiry = Something"; 


try {
    $dbh = new PDO('login info here');
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':user_id', $userID, PDO::PARAM_INT);     
    $stmt->bindParam(':licence',$licence, PDO::PARAM_STR);
    $stmt->bindParam(':expiry',$expiry, PDO::PARAM_STR);                            
    $stmt->execute();
    //$stmt->closeCursor(); //use this instead of $dbh = null   if you will continue with another DB function
    $dbh = null; 
    }

    catch(PDOException $e)
    {
    $error=$e->getMessage();        
    }

Any help is much appreciated.

eggyal · Accepted Answer · 2012-07-09T06:43:27.840

You can use MySQL's VALUES() function:

In an INSERT ... ON DUPLICATE KEY UPDATE statement, you can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the statement. In other words, VALUES(col_name) in the UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred.

Therefore, in your case:

ON DUPLICATE KEY UPDATE expiry = VALUES(expiry)

Alternatively, you can create a fourth parameter to which you bind $expiry again:

$sql = "INSERT INTO $table (user_id, licence, expiry)
                        VALUES (
                        :user_id,
                        :licence,
                        :expiry)
    ON DUPLICATE KEY UPDATE expiry = :another";


try {
    $dbh = new PDO('login info here');
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':user_id', $userID , PDO::PARAM_INT);
    $stmt->bindParam(':licence', $licence, PDO::PARAM_STR);
    $stmt->bindParam(':expiry' , $expiry , PDO::PARAM_STR);
    $stmt->bindParam(':another', $expiry , PDO::PARAM_STR);
    $stmt->execute();
    // etc.

This might be a stupid question, in the case of a duplicate, can I get the existing expiry value and use that to calculate the updated expiry using ON DUPLICATE KEY UPDATE ? — TryHarder, Jul 09 '12 at 06:47
@moomoochoo: Ugh, I had completely missed that point from the original question. Yes, you can - exactly as you were originally proposing: `... UPDATE expiry = expiry + INTERVAL 2 YEAR`. This will, of course, work from PDO just as with any other MySQL client. — eggyal, Jul 09 '12 at 06:49

score -1 · Answer 2 · answered Feb 23 '13 at 08:57

I know you have answer below, but i had same problem and my solution looks quite different but it works for me so if you want to use different statement of using insert in mysql with explicit binding values to columns you can try this code

$sql = "
   INSERT INTO 
      $table 
   SET
      user_id = :user_id,
      licence = :licence,
      expiry  = :expiry
   ON DUPLICATE KEY UPDATE 
      expiry = :expiry
";

$dbh = new PDO('login info here');
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$stmt = $dbh->prepare($sql);
$stmt->bindValue('user_id', $userID , PDO::PARAM_INT);
$stmt->bindValue('licence', $licence, PDO::PARAM_STR);
$stmt->bindValue('expiry' , $expiry , PDO::PARAM_STR);

This "solution" will not work by default, PDO does not allow to reuse bind params, check this answer for more information https://stackoverflow.com/a/7604080/9492722 — FdelS, Jul 27 '23 at 17:12

How can I use ON DUPLICATE KEY UPDATE in PDO with mysql?

2 Answers2

Linked