提问人:JCprog 提问时间:10/3/2022 最后编辑:JCprog 更新时间:10/6/2022 访问量:147
为什么更新 MySQL 表中的数据在第 1 行抛出错误 1 SQL 语法
Why is Updating Data In MySQL Table throws Error 1 SQL syntax at line 1
问:
我正在尝试在一个提交按钮上更新数据库。代码似乎有效并看到更改,但给出错误(您的 SQL 语法有错误......在第 1 行的“1”附近使用) 我整晚都在搜索这个网站和其他人,寻找同样的错误,但仍然无法修复它。 是什么原因导致了这个错误,我该如何解决它?请帮忙!
<?php
require_once 'config.php';
// Create connection
$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// prepare and bind
$sql = "UPDATE Bank SET
Quantity=?,
Category =?,
Item=?,
UnitPrice=?,
TOTAL=?,
MiscMat=?,
LaborHours=?,
LaborRate=?
WHERE QID=?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ssssssssd',
$db02,
$db03,
$db04,
$db05,
$db06,
$db07,
$db08,
$db09,
$db18);
// set parameters and execute
$db02 = $_POST['inp02']; // Quantity
$db03 = $_POST['inp03']; // Category
$db04 = $_POST['inp04']; // Item
$db05 = $_POST['inp05']; // UnitPrice
$db06 = $_POST['inp06']; // TOTAL
$db07 = $_POST['inp07']; // MiscMat
$db08 = $_POST['inp08']; // LaborHours
$db09 = $_POST['inp09']; // LaborRate
$db18 = $_POST['inp18']; // QID
$stmt->execute();
$i = 0;
$count = count($db02);
for($i=0; $i < $count; $i++){
$currentQty = ($db02[$i]);
$currentDesc = ($db03[$i]);
$currentPrice = ($db04[$i]);
$currentID = ($db18[$i]);
$update = mysqli_query($conn, "UPDATE `Bank` SET
`Quantity` = '".$currentQty."',
`Category` = '".$currentDesc."',
`Item` = '".$currentPrice."'
WHERE `QID` = '".$currentID ."' ");
}
if (mysqli_query($conn, $update)) {
echo "<script>location.href = 'somewhere.php';</script>";
} else {
echo "Error: " . $update . "<br>" . mysqli_error($conn);
}
$stmt->close();
$conn->close();
?>
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr>
<td><input type='hidden' class='db01' name='inp01[]' value='".$row["ID"]."' readonly> </td>
<td><input type='text' class='db02' name='inp02[]' value='".$row["Quantity"]."'> </td>
<td><input type='text' class='db03' name='inp03[]' value='".$row["Category"]."'> </td>
<td><input type='text' class='db04' name='inp04[]' value='".$row["Item"]."'> </td>
<td><input type='number' step='any' class='db05' name='inp05[]' value='".$row["UnitPrice"]."'> </td>
<td><input type='number' step='any' class='db06' name='inp06[]' value='".$row["TOTAL"]."'> </td>
<td><input type='number' step='any' class='db07' name='inp07[]' value='".$row["MiscMat"]."'> </td>
<td><input type='number' step='any' class='db08' name='inp08[]' value='".$row["LaborHours"]."'> </td>
<td><input type='number' step='any' class='db09' name='inp09[]' value='".$row["LaborRate"]."'> </td>
<td>
<input type='hidden' class='db10' name='inp10[]' value='".$row["QNumber"]."'>
<input type='hidden' class='db11' name='inp11[]' value='".$row["Date"]."'>
<input type='hidden' class='db12' name='inp12[]' value='".$row["Company"]."'>
<input type='hidden' class='db13' name='inp13[]' value='".$row["Address1"]."'>
<input type='hidden' class='db14' name='inp14[]' value='".$row["Address2"]."'>
<input type='hidden' class='db15' name='inp15[]' value='".$row["ATTN"]."'>
<input type='hidden' class='db16' name='inp16[]' value='".$row["Project"]."'>
<input type='hidden' class='db17' name='inp17[]' value='".$row["Email"]."'>
<input type='hidden' class='db18' name='inp18[]' value='".$row["QID"]."'>
</td>
</tr>";
}
} else {
echo "0 results";
}
mysqli_close($conn);
?>
答:
1赞
Phil
10/3/2022
#1
预准备语句可以创建一次,并通过引用绑定其参数,这意味着您可以在分配任何值之前绑定它们。
然后,您可以为这些参数变量赋值,并根据需要多次执行该语句。
确保 MySQLi 设置为引发异常。这意味着您无需手动检查错误。
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
准备您的对账单
$update = <<<_SQL UPDATE `Bank` SET `Quantity` = ?, `Category` = ?, `Item` = ?, `UnitPrice` = ?, `TOTAL` = ?, `MiscMat` = ?, `LaborHours` = ?, `LaborRate` = ? WHERE `QID` = ? _SQL; $stmt = $conn->prepare($update);
绑定参数
$stmt->bind_param( 'ssssssssd', $quantity, $category, $item, $unitPrice, $total, $miscMat, $laborHours, $laborRate, $qid );
循环,分配变量值并执行
$count = count($_POST['inp18']); for ($i = 0; $i < $count; $i++) { $quantity = $_POST['inp02'][$i]; $category = $_POST['inp03'][$i]; $item = $_POST['inp04'][$i]; $unitPrice = $_POST['inp05'][$i]; $total = $_POST['inp06'][$i]; $miscMat = $_POST['inp07'][$i]; $laborHours = $_POST['inp08'][$i]; $laborRate = $_POST['inp09'][$i]; $qid = $_POST['inp18'][$i]; $stmt->execute(); } header('Location: somewhere.php'); exit;
评论
0赞
JCprog
10/3/2022
感谢您的代码,现在我收到此错误:错误:UPDATE SET = ?, = ?, = ?, = ?, = ?, = ?, = ?, = ?, = ?哪里 = ?您的 SQL 语法有错误;查看与您的MySQL服务器版本相对应的手册,了解在第3行的'?, = ?, = ?, = ?, = ?, 'Mis'附近使用的正确语法Bank
Quantity
Category
Item
UnitPrice
TOTAL
MiscMat
LaborHours
LaborRate
QID
Category
Item
UnitPrice
TOTAL
0赞
Phil
10/3/2022
@JCprog对我来说,你仍然在使用你绝对不应该做的事情mysqli_query()
0赞
JCprog
10/3/2022
成功了!我永远不会接近你的代码。这是一次很棒的学习经历,非常感谢!
评论
$update
$db02
$db03
UPDATE
Bank
UPDATE
$db*
$_POST