问题描述
详情
我正在为新的或更新的许可证的到期做一个插入.有效期为自插入之日起 2 年.如果检测到重复，条目将被更新，使得到期时间等于剩余到期时间加上 2 年.
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.
关于重复，在下面的例子中应该只有一行包含 user_id =55 和 licence=commercial.
Regarding duplicates, in the example below there should only be one row containing user_id =55 and licence=commercial.
表格: licence_expiry
TABLE: licence_expiry
--------------------------------------------------------
| user_id | licence | expiry |
--------------------------------------------------------
| 55 | commercial | 2013-07-04 05:13:48 |
---------------------------------------------------------
user_id (int11), license (varchan50), expiry (DATETIME)
user_id (int11), licence (varchan50), expiry (DATETIME)
我认为在 mysql 中你会这样写(请注意，我还没有检查代码在 mysql 中是否有效.)
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
问题:如何使用 PDO 做到这一点?我已经写了一个我认为我将使用的粗略大纲，但我不确定要为 ON DUPLICATE KEY UPDATE 的到期值写什么.
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();
}
非常感谢任何帮助.
推荐答案
你可以使用 MySQL 的 VALUES() 函数:
You can use MySQL's VALUES() function:
在 INSERT ... ON DUPLICATE KEY UPDATE 语句，可以使用 VALUES(col_name) 函数/doc/en/update.html">UPDATE 子句引用来自 INSERT 语句的部分.换句话说，VALUES(col_name)update.html">UPDATE 子句指的是要插入的 col_name 的值，没有发生重复键冲突.
In an
INSERT ... ON DUPLICATE KEY UPDATEstatement, you can use theVALUES(col_name)function in theUPDATEclause to refer to column values from theINSERTportion of the statement. In other words,VALUES(col_name)in theUPDATEclause refers to the value ofcol_namethat would be inserted, had no duplicate-key conflict occurred.
因此，就您而言:
ON DUPLICATE KEY UPDATE expiry = VALUES(expiry)
或者，您可以创建第四个参数，再次将 $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.
这篇关于如何在带有 mysql 的 PDO 中使用 ON DUPLICATE KEY UPDATE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！