MySQL使用PHP将DATETIME更新为NOW（）

This all worked before I added the section to update "last_update".

在我添加更新“last_update”的部分之前,这一切都有效。

if((time() - $last_update) > 7200){
$sql = $dbh->prepare("UPDATE item_list SET quantity=:quantity, price=:price, last_update=:now WHERE item_name=:itemname");
                $sql->bindParam(':quantity', $json->volume);
                $sql->bindParam(':price', $json->lowest_price);
                $sql->bindParam(':itemname', $row['Item_Name']);
                $sql->bindParam(':now', "NOW()");  //This doesn't work
                $sql->execute();
}

When this is called I want to make last_update the date and time now. In the database it is currently a DATETIME, and when I last_update I origianly set them to NOW();

当调用它时,我想立即将last_update设为日期和时间。在数据库中它当前是DATETIME,当我last_update时,我原始地将它们设置为NOW();

Doing this I get the error Fatal error: Cannot pass parameter 2 by reference in.... Directory

这样做我得到错误致命错误:无法通过....目录中的引用传递参数2

I know it expects a variable, I'm not sure how to fix it though. I tried setting

我知道它需要一个变量,但我不知道如何修复它。我尝试过设置

$now = "NOW()"; $sql->bindParam(':now', $now);

$ now =“NOW()”; $ sql-> bindParam(':now',$ now);

No prevail. Any help?

没有优势。有帮助吗?

3 个解决方案

#1

Why you need to bind, just put NOW() directly

为什么需要绑定,只需直接放入NOW()

$sql = $dbh->prepare("UPDATE item_list SET quantity=:quantity, price=:price, last_update=now() WHERE item_name=:itemname");

#2

If your last_update column is looking for a UNIX timestamp, then do :

如果您的last_update列正在查找UNIX时间戳,那么执行以下操作:

$now = time(); 
$sql->bindParam(':now', $now);

If it's after a different time format, use date(), and the relevant formatting it has to set the date and time

如果它采用不同的时间格式,请使用date(),并使用相关格式设置日期和时间

#3

You can keep your bind query as it is & remove the last_update column from the query.

您可以按原样保留绑定查询,并从查询中删除last_update列。

Since you are updating other things in the record via another query, then you can set the default value of the field last_update to CURRENT_TIMESTAMP & set it's attribute as ON UPDATE CURRENT_TIMESTAMP. That will ensure it automatically updates itself with the current time now() whenever that record is updated.

由于您通过另一个查询更新记录中的其他内容,因此您可以将字段last_update的默认值设置为CURRENT_TIMESTAMP并将其属性设置为ON UPDATE CURRENT_TIMESTAMP。这将确保每当更新该记录时,它会使用当前时间now()自动更新。

It wouldn't be the best thing to remove bind() as you rightly said to prevent SQL injection attempts.

删除bind()并不是最好的事情,因为你正确地说要阻止SQL注入尝试。

#1