gpt4 book ai didi

PHP/MYSQL INSERT ON DUPLICATE KEY 失败

转载 作者:行者123 更新时间:2023-11-30 23:19:58 24 4
gpt4 key购买 nike

我只是在修补一个简单的时间表脚本,如果我对数据库进行简单的插入,一切都很好。我也可以更新条目没问题,我的问题是我想更新并保留信息(这与我使用的 CONCAT 一起工作正常)但是一旦我使用 ON DUPLICATE KEY UPDATE 它就会中断。

$sql="INSERT INTO sheet(employee, workdate, location, description, timein, timeout, timespent)
VALUES('$employee', CURDATE(), '$location', '$description', '$timein', '$timeout', '$timespent')";
//ON DUPLICATE KEY UPDATE
//location=VALUES(location),
//description=VALUES(description),
//timein=VALUES(timein),
//timeout=VALUES(timeout),
//timespent=VALUES(timespent)
//WHERE employee=$employee";
echo $sql;echo "<br>";
mysql_query($sql)or die(mysql_error());

如果我取消对重复键的注释,我会收到“您的 SQL 语法有错误;请检查与您的 MySQL 服务器版本对应的手册,了解在第 9 行的‘WHERE employee=‘Eric’’附近使用的正确语法“错误。我唯一能想到的是我在一张完全空的 table 上做这件事,但我认为 INSERT 会解决这个问题。我所有的 $variables 都是干净的(没有 PDO,也必须学习)只是 real_escape_string

$sql="INSERT INTO sheet(employee, workdate, location, description, timein, timeout, timespent)
VALUES('$employee', CURDATE(), '$location', '$description', '$timein', '$timeout', '$timespent')
ON DUPLICATE KEY UPDATE
location=VALUES(location),
description=VALUES(description),
timein=VALUES(timein),
timeout=VALUES(timeout),
timespent=VALUES(timespent)
WHERE employee='$employee'";
echo $sql;echo "<br>";
mysql_query($sql)or die(mysql_error());

最佳答案

根据 INSERT ... ON DUPLICATE KEY UPDATE 的文档,查询末尾不应有 WHERE 语句:

$sql="INSERT INTO sheet(employee, workdate, location, description, timein, timeout, timespent)
VALUES('$employee', CURDATE(), '$location', '$description', '$timein', '$timeout', '$timespent')
ON DUPLICATE KEY UPDATE
location=VALUES(location),
description=VALUES(description),
timein=VALUES(timein),
timeout=VALUES(timeout),
timespent=VALUES(timespent)";



备注:

Recommended API It is recommended to use either the mysqli or PDO_MySQL extensions. It is not recommended to use the old mysql extension for new development, as it has been deprecated as of PHP 5.5.0 and will be removed in the future. A detailed feature comparison matrix is provided below. The overall performance of all three extensions is considered to be about the same. Although the performance of the extension contributes only a fraction of the total run time of a PHP web request. Often, the impact is as low as 0.1%.

  • 如果您还没有这样做,您必须在查询中使用文本变量之前正确地转义它们:

    $employee = mysql_real_escape_string($employee);
    // and so on with the other variables

阅读How does this SQL injection work?Why shouldn't I use mysql_* functions in PHP?了解有关这些主题的更多信息。

关于PHP/MYSQL INSERT ON DUPLICATE KEY 失败,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/15932551/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com