我有下面的代码来更新(更多的'添加到')数据库中的一行。我读过一些非常相似的帖子，但仍然看不出我错在哪里…
我得到错误:
你的SQL语法有错误;查看与MySQL服务器版本对应的手册，以便在第1行
的''EventID' = '2' WHERE GameID = 'E2V1D24M10Y2015' AND PlayerID = '55''附近使用正确的语法。
在此例中，'E5V7D24M10Y2015'为$GameID的值。我正在寻找一个名为GameID的列，其值为E5V7D24M10Y2015，而不是该名称的列。请告诉我为什么我的$sql正在寻找一个以它正在寻找的值命名的列。
每次函数运行时，count($Runners)将不同，每个变量中的值将不同。这就是为什么我有SQL在一个循环。
if ($formtype == "gameresults"){
$Runners = $_POST['runners'];
$event = $_POST['event'];
$eid = $_POST['eid'];
$vid = $_POST['vid'];
$GameID = $_POST['GameID'];
$date = $_POST['date'];
$season = $_POST['season'];
$region = $_POST['region'];
$notes = $_POST['notes'];
$kev = "kev@email.com";
$email = $_POST['manager'];
$notes = wordwrap($notes,70);
$subject = ("Results for " . $event);
$tix = "";
$cashs = "";
for ($x = 1; $x < ($Runners + 1); $x++){
$ID = $_POST['ID' . $x];
$Name = $_POST['Name' .$x];
$Place = $_POST['Place'.$x];
$Points = $_POST['Points'.$x];
$Cash = $_POST['Cash'.$x];
$Ticket = $_POST['Ticket'.$x];
$vn = $_POST['vn'];
$buyin = $_POST['buyin'];
$data = array($eid,$vid,$region,$buyin,$GameID,$date,$season,$ID,$Name,$Place,$Points,$Ticket,$Cash,$Runners);
$fields = array('EventID','VenueID','Region','Buyin','GameID','Date','Season','PlayerID','Name','Position','Points','Ticket?','Cash','Runners');
for ($x = 0; $x < (count($data) - 1); $x++){
$sql = "UPDATE results SET '$fields[$x]' = '$data[$x]' WHERE GameID = '$GameID' AND PlayerID = '$ID'";
$res = mysqli_query($dbcon, $sql) or die("Update failed. <br>" . mysqli_error($dbcon));
}
您可以修改代码使其更清晰。我做了一个例子，但我没有映射所有的字段，所以你需要调整它。
$eid = 4;
$vid = 5;
$region = 'aa';
$buyin = 'asd';
$gameID = 5;
$date = 5;
$playerID = 10;
$fields = array(
'EventID' => $eid,
'VenueID' => $vid,
'Region' => $region,
'Buyin' => $buyin,
'GameID' => $gameID,
'PlayerID'=> $playerID,
'Date' => $date,
);
$numItems = count($fields);
$query = "UPDATE `results` SET ";
$i = 0;
foreach($fields as $name => $value) {
++$i;
if($name == 'GameID' || $name == 'PlayerID') {
continue;
}
$query .= sprintf(" `%s` = '%s'%s ", $name, $value, ($i === $numItems ? "": ","));
}
$query .= sprintf(" WHERE `GameID` = '%d' AND `PlayerID` = '%d'", $fields['GameID'], $fields['PlayerID']);
echo $query;
字段可以通过$key => $value进行映射，然后在foreach循环中使用。此外，使用sprintf()使代码更易于阅读。然而，最好的选择是使用预处理语句。
您也不需要进行多个UPDATE查询，只需在一个查询中设置更多参数。
$data = array($eid,$vid,$region,$buyin,$GameID,$date,$season,$ID,$Name,$Place,$Points,$Ticket,$Cash,$Runners);
$fields = array('EventID','VenueID','Region','Buyin','GameID','Date','Season','PlayerID','Name','Position','Points','Ticket?','Cash','Runners');
for ($x = 0; $x < (count($data) - 1); $x++){
$sql = "UPDATE results SET " . $fields[$x] . " = '$data[$x]' WHERE results.GameID = $GameID AND results.PlayerID = $ID";
$res = mysqli_query($dbcon, $sql) or die("Update failed. <br>" . mysqli_error($dbcon));
}