我有一个页面,其中有多个简单的输入供用户选择信息更新选项。用户可能还没有定义更新配置文件,所以我尝试使用"INSERT INTO…"重复键更新"语句。我也试图用sprintf这样做,以避免可能的SQL注入问题。我的问题是我不能开始弄清楚如何编写SQL语句。下面是相关的代码。
$updateSQL = sprintf("INSERT INTO reminders_cfg
(id, day_of, day_of_advance, day_prior, default_time, week_prior, 2_week_prior, 3_week_prior, 4_week_prior, 5_week_prior, day_after, 2_week_after, 50_day_after)
VALUES %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
ON DUPLICATE KEY UPDATE ",
GetSQLValueString($jeweler, "text"),
GetSQLValueString($_POST['DayOf'], "text"),
GetSQLValueString($_POST['Advance'], "text"),
GetSQLValueString($_POST['DayPrior'], "text"),
GetSQLValueString($_POST['Time'], "text"),
GetSQLValueString($_POST['WeekPrior'], "text"),
GetSQLValueString($_POST['2WeeksPrior'], "text"),
GetSQLValueString($_POST['3WeeksPrior'], "text"),
GetSQLValueString($_POST['4WeeksPrior'], "text"),
GetSQLValueString($_POST['5WeeksPrior'], "text"),
GetSQLValueString($_POST['DayAfter'], "text"),
GetSQLValueString($_POST['2WeeksAfter'], "text"),
GetSQLValueString($_POST['50DaysAfter'], "text"));
这是在Dreamweaver中完成的PHP页面。这就是GetSQLValueString函数的用武之地。我真的无法弄清楚在实际的"ON DUPLICATE KEY UPDATE"部分之后该怎么做,而不必再次复制所有变量和占位符。我希望这是有意义的。
更新:好的。这是我最近的一次尝试,但仍然不起作用。
if ((isset($_POST["MM_update"])) && ($_POST["MM_update"] == "form1")) {
$stmt = $mysqli->prepare("INSERT INTO reminders_cfg
(id, day_of, day_of_advance, day_prior, default_time, week_prior, 2_week_prior, 3_week_prior, 4_week_prior, 5_week_prior, day_after, 2_week_after, 50_day_after)
VALUES :id, :day_of, :day_of_advance, :day_prior, :default_time, :week_prior, :2_week_prior, :3_week_prior, :4_week_prior, :5_week_prior, :day_after, :2_week_after, :50_day_after
ON DUPLICATE KEY UPDATE day_of = :day_of, day_of_advance = :day_of_advance, day_prior = :day_prior, default_time = :default_time, week_prior = :Week_prior, 2_week_prior = :2_week_prior, 3_week_prior = :3_week_prior, 4_week_prior = :4_week_prior, 5_week_prior = :5_week_prior, day_after = :day_after, 2_week_after = :2_week_after, 50_day_after = :50_day_after")
or die($mysqli->error);
$stmt->bindParam(':id', $jeweler, PDO::PARAM_INT);
$stmt->bindParam(':day_of', $_POST['DayOf'], PDO::PARAM_STR, 6);
$stmt->bindParam(':day_of_advance', $_POST['Advance'], PDO::PARAM_INT);
$stmt->bindParam(':day_prior', $_POST['DayPrior'], PDO::PARAM_STR, 6);
$stmt->bindParam(':default_time', $_POST['Time'], PDO::PARAM_STR, 9);
$stmt->bindParam(':week_prior', $_POST['WeekPrior'], PDO::PARAM_STR, 6);
$stmt->bindParam(':2_week_prior', $_POST['2WeeksPrior'], PDO::PARAM_STR, 6);
$stmt->bindParam(':3_week_prior', $_POST['3WeeksPrior'], PDO::PARAM_STR, 6);
$stmt->bindParam(':4_week_prior', $_POST['4WeeksPrior'], PDO::PARAM_STR, 6);
$stmt->bindParam(':5_week_prior', $_POST['5WeeksPrior'], PDO::PARAM_STR, 6);
$stmt->bindParam(':day_after', $_POST['DayAfter'], PDO::PARAM_STR, 6);
$stmt->bindParam(':2_week_after', $_POST['2WeeksAfter'], PDO::PARAM_STR, 6);
$stmt->bindParam(':50_day_after', $_POST['50DaysAfter'], PDO::PARAM_STR, 6);
$stmt->execute() or die("Insert query error: " . mysql_error());
$stmt->close();
}
这是产生一个SQL语法错误,因为我是新的这个'INSERT INTO…ON DUPLICATE KEY UPDATE’我有点卡住了。这里是错误…
你的SQL语法有错误;查看与MySQL服务器版本对应的手册,以便在第3行
在ON DUPLICATE KEY UPDATE
之后,您只需放置想要更新的值,并保留应该保持不变的值。如
insert into my_table(name, lastSeen, salary)
values("Some Name", now(), 25334)
on duplicate key update salary = 25334, lastSeen = now()
name
就是本例中的primary key
现在假设数据库中已经存在一条同名的记录,该条目将通过更改salary和lastSeen来更新。如果没有,将使用提供的值创建条目。在语句的第一部分
insert into my_table(name, lastSeen, salary)
values("Some Name", now(), 25334)
希望对你有帮助。
除了如果你在你的问题中添加了你想要更新的重复键的字段,那么我们可以通过使用你的代码来回答你的问题。
编辑好吧,我知道这不是最好的格式代码,但也许这有帮助?(可能有语法错误,但如果这是你想要的,你可能会明白)
$updateSQL = sprintf("INSERT INTO reminders_cfg
(id, day_of, day_of_advance, day_prior, default_time,
week_prior, 2_week_prior, 3_week_prior, 4_week_prior,
5_week_prior, day_after, 2_week_after, 50_day_after)
VALUES %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
ON DUPLICATE KEY UPDATE id = %s, day_of = %s, day_of_advance = %s,
day_prior = %s, default_time = %s, week_prior = %s,
2_week_prior = %s, 3_week_prior = %s, 4_week_prior = %s,
5_week_prior = %s, day_after = %s, 2_week_after = %s,
50_day_after = %s ",
GetSQLValueString($jeweler, "text"),
GetSQLValueString($_POST['DayOf'], "text"),
GetSQLValueString($_POST['Advance'], "text"),
GetSQLValueString($_POST['DayPrior'], "text"),
GetSQLValueString($_POST['Time'], "text"),
GetSQLValueString($_POST['WeekPrior'], "text"),
GetSQLValueString($_POST['2WeeksPrior'], "text"),
GetSQLValueString($_POST['3WeeksPrior'], "text"),
GetSQLValueString($_POST['4WeeksPrior'], "text"),
GetSQLValueString($_POST['5WeeksPrior'], "text"),
GetSQLValueString($_POST['DayAfter'], "text"),
GetSQLValueString($_POST['2WeeksAfter'], "text"),
GetSQLValueString($_POST['50DaysAfter'], "text"),
GetSQLValueString($jeweler, "text"),
GetSQLValueString($_POST['DayOf'], "text"),
GetSQLValueString($_POST['Advance'], "text"),
GetSQLValueString($_POST['DayPrior'], "text"),
GetSQLValueString($_POST['Time'], "text"),
GetSQLValueString($_POST['WeekPrior'], "text"),
GetSQLValueString($_POST['2WeeksPrior'], "text"),
GetSQLValueString($_POST['3WeeksPrior'], "text"),
GetSQLValueString($_POST['4WeeksPrior'], "text"),
GetSQLValueString($_POST['5WeeksPrior'], "text"),
GetSQLValueString($_POST['DayAfter'], "text"),
GetSQLValueString($_POST['2WeeksAfter'], "text"),
GetSQLValueString($_POST['50DaysAfter'], "text"));
如果有一个可能的碰撞键,您应该只使用ON DUPLICATE KEY UPDATE
。在这种情况下,我猜它是ID。
你最好定义一个触发器。像这样:
CREATE TRIGGER `reminders_cfg_duplicates` BEFORE INSERT ON `reminders_cfg` FOR EACH ROW
DELETE FROM `reminders_cfg` WHERE `id`=new.`id`;
运行一次,例如在phpMyAdmin的控制面板上。然后,数据库将自动删除任何导致重复的行,从而允许插入新行,而不必在更新中复制所有值。