记录更新错误

Question

我正在尝试更新表中的特定行;但是，当查询运行时，它会更新最后添加的记录，而不是所选的记录。

SQL语句直接取自phpmyAdmin。我试过"UPDATE registration_tbl SET Paid =' Paid ' WHERE ID='$row21'"但这仍然行不通。

我在代码中放错了什么吗?

<table class="table table-striped table-hover table table-responsive-sm table-responsive-md table-responsive-lg">
<tr>
<th>Title</th>
<th>First Name</th>
<th>Last Name</th>
<th>Sex</th>
<th>Age</th>
<th>Address Type</th>
<th>Address 1</th>
<th>Address 2</th>
<th>Home</th>
<th>Work</th>
<th>Cell</th>
<th>Email Address</th>
<th>Congregation</th>
<th>RMC</th>
<th>Auxillary</th>
<th>Occupation</th>
<th>Category</th>
<th>Username</th>
<th>Submission Date</th>
<th>Payment Status</th>
<th>Action</th>
</tr>
<?php
$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
$result_set = mysqli_query($conn,"SELECT * FROM registration_tbl");   
$num_messages = mysqli_num_rows($result_set);
$num = 0;
while($row = mysqli_fetch_array($result_set))
{
$row1 = $row["Title"];
$row2 = $row["FirstName"];
$row3 = $row["LastName"];
$row4 = $row["Sex"];
$row5 = $row["Age"];
$row6 = $row["AddressType"];
$row7 = $row["Address1"];
$row8 = $row["Address2"];
$row9 = $row["Home"];
$row10 = $row["Work"];
$row11 = $row["Cell"];
$row12 = $row["EmailAdd"];
$row13 = $row["Congregation"];
$row14 = $row["RMC"];
$row15 = $row["Auxillary"];
$row16 = $row["Occupation"];
$row17 = $row["Category"];
$row18 = $row["Username"];
$row19 = $row["DateSubmitted"];
$row20 = $row["Paid"];
$row21 = $row["ID"];

$num++;
echo "<tr>";
echo "<td>$row1</td>";
echo "<td>$row2</td>";
echo "<td>$row3</td>";
echo "<td>$row4</td>";
echo "<td>$row5</td>";
echo "<td>$row6</td>";
echo "<td>$row7</td>";
echo "<td>$row8</td>";
echo "<td>$row9</td>";
echo "<td>$row10</td>";
echo "<td>$row11</td>";
echo "<td>$row12</td>";
echo "<td>$row13</td>";
echo "<td>$row14</td>";
echo "<td>$row15</td>";
echo "<td>$row16</td>";
echo "<td>$row17</td>";
echo "<td>$row18</td>";
echo "<td>$row19</td>";
echo "<td>$row20</td>";
if($row20 != "PAID")
{
echo "<td><input type='submit' class='btn btn-success' name='paid' value='PAID' /></td></tr>";
}

}
echo "</table></br>";
echo "<table><tr><td>";
echo $num_messages . " Registration(s) Found!";
echo "</td></tr></table>";
if(isset($_POST['paid']))
{
$conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
$updatePaymentStmt = "UPDATE `registration_tbl` SET `Paid` = 'PAID' WHERE `registration_tbl`.`ID` = $row21;";
if(mysqli_query($conn, $updatePaymentStmt))
{
echo "<script>alert('Payment updated successfully!')</script>";
}
else
{
echo "<script>alert('Error in updating Payment!')</script>";
}
}

Answer 1

我想你会想要一个单独的表格中的每一行。您还需要在表单中包含一个包含ID的隐藏字段，以便服务器在接收到提交时知道要处理哪个ID。

删除任何<form>...</form>标签，你可能已经放置环绕整个表，而不是使用:

if($row20 != "PAID")
{
echo "<td><form action='' method='post'><input type='submit' class='btn btn-success' name='paid' value='PAID' /><input type='hidden' name='id' value='".$row["ID"]."'/></form></td></tr>";
}

和

if(isset($_POST['paid']))
{
$id = $_POST["id"];
///etc, you can now use $id in a parameter in your query, to select the correct row

---

注:正如其他人在评论中提到的那样，其余的代码也可以大大简化，并且您应该明确修复SQL注入问题-这是一个严重的安全问题。

Answer 2

这个bug来自于您的思维缺陷，而不是代码中的意外行为。

实际上，您有一个while循环，迭代整个结果集(从第一个查询开始)并更新$row*变量。这意味着$row21总是最后选择的记录。如果你在最后丢弃一个ORDER BY id DESC，你会发现第一个记录总是被更新…

所以你实际上想要做的是将id添加到按钮中-并使每个按钮都有自己的表单-这样当表单发布时，预期的id就在按钮的值中。

类似:

<?php
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
$registrations = $mysqli->query($conn,"SELECT * FROM registration_tbl");   
$num_messages  = $registration->num_rows;
while ($row = $registrations->fetch_assoc() {
echo "<tr>";
echo "<td>{$row["Title"]}</td>";
echo "<td>{$row["FirstName"]}</td>";
echo "<td>{$row["LastName"]}</td>";
echo "<td>{$row["Sex"]}</td>";
echo "<td>{$row["Age"]}</td>";
echo "<td>{$row["AddressType"]}</td>";
echo "<td>{$row["Address1"]}</td>";
echo "<td>{$row["Address2"]}</td>";
echo "<td>{$row["Home"]}</td>";
echo "<td>{$row["Work"]}</td>";
echo "<td>{$row["Cell"]}</td>";
echo "<td>{$row["EmailAdd"]}</td>";
echo "<td>{$row["Congregation"]}</td>";
echo "<td>{$row["RMC"]}</td>";
echo "<td>{$row["Auxillary"]}</td>";
echo "<td>{$row["Occupation"]}</td>";
echo "<td>{$row["Category"]}</td>";
echo "<td>{$row["Username"]}</td>";
echo "<td>{$row["DateSubmitted"]}</td>";
echo "<td>{$row["Paid"]}</td>";
echo $row["Paid"]] !== "PAID" ?
"<td><form method='post'><button class='btn btn-success' name='paid' value='{$row["ID"]}'>Paid</button></form></td>" :
"<td></td>";
}
echo "</tr>";
}
echo "</table></br>";
echo "<table><tr><td>";
echo $num_messages . " Registration(s) Found!";
echo "</td></tr></table>";
if ($_POST['paid'] ?? null) {
$sql   = "UPDATE `registration_tbl` SET `Paid` = 'PAID' WHERE `registration_tbl`.`ID` = ?";
$query = $mysqli->prepare($sql);
$query->bind_param("i", $_POST["paid"]);

echo $query->execute() ?
"<script>alert('Payment updated successfully!')</script>" :
"<script>alert('Error in updating Payment!')</script>";
}
}