本文介绍了如何在PDO中使用ON DUPLICATE KEY UPDATE与mysql?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

详细信息



我正在为新的或续订的许可证到期的单次插入。到期时间为插入日期2年。
如果检测到重复,则该条目将被更新,使得到期时间等于剩余到期时间加上2年。



关于在下面的示例中,应该只有一行包含user_id = 55和license = commercial。



TABLE: licence_expiry

  ------------------------------ -------------------------- 
| user_id |许可证|到期|
---------------------------------------------- ----------
| 55 |商业| 2013-07-04 05:13:48 |
---------------------------------------------- -----------

user_id(int11),license(varchan50),到期(DATETIME)



我认为在mysql中你会写这样的东西(请注意我没有检查这个代码是否可以在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
/ pre>

问题:如何使用PDO执行此操作?我写了一个粗略的概要,我想我将会使用什么,但是我不知道为 ON DUPLICATE KEY UPDATE的到期值写什么。

  $ sql =INSERT INTO $ table(user_id,license,expiry)
VALUES(
:user_id,
:license ,
: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(':license',$ license,PDO :: PARAM_STR);
$ stmt-> bindParam(':expiry',$ expiry,PDO :: PARAM_STR);
$ stmt-> execute();
// $ stmt-> closeCursor(); //如果您继续使用另一个DB函数,则使用此代替$ dbh = null
$ dbh = null;
}

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

任何帮助都非常感激。

解决方案

您可以使用MySQL的功能:

因此在你的情况下:

  ON DUPLICATE KEY UPDATE expiry = VALUES(expiry)

或者,您可以创建一个第四个参数,您再次绑定 $ expiry

  $ sql =INSERT INTO $ table(user_id,license,expiry)
VALUES(
:user_id,
:license,
: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(':license',$ license,PDO :: PARAM_STR);
$ stmt-> bindParam(':expiry',$ expiry,PDO :: PARAM_STR);
$ stmt-> bindParam(':another',$ expiry,PDO :: PARAM_STR);
$ stmt-> execute();
//等


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.

解决方案

You can use MySQL's VALUES() function:

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.

这篇关于如何在PDO中使用ON DUPLICATE KEY UPDATE与mysql?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-26 06:53