我在更新数据库时遇到问题。我的 sql 更新语句有问题吗？我检查了sql语句，它说数据库中没有记录。我不知道该怎么办。
<!-- template for mySql database access. -->
<!DOCTYPE html>
<html>
<head>
<title>CRUD</title>
<link href="/sandvig/mis314/assignments/style.css" rel="stylesheet" type="text/css">
</head>
<div class="pageContainer centerText">
<h3>CRUD (Create, Read, Update, & Delete) Database</h3>
<?php
//include database connection
include("DatabaseConnection2.php");
//connect to database
$link = fConnectToDatabase();
//Retrieve parameters from querystring and sanitize
$nameF = fCleanString($link, $_GET['nameF'], 15);
$nameL = fCleanString($link, $_GET['nameL'], 15);
$deleteID = fCleanNumber($_GET['deleteID']);
$updateID = fCleanNumber($_GET['updateID']);
$updateID2 = fCleanNumber($_GET['updateID2']);
//Populate Textbox
if (!empty($updateID)) {
$sql = "SELECT NameL, NameF
FROM customertbl
WHERE custID = '$updateID'";
mysqli_query($link, $sql) or die('Delete error: ' . mysqli_error($link));
$result = mysqli_query($link, $sql)
or die('SQL syntax error: ' . mysqli_error($link));
$row = mysqli_fetch_array($result);
$strFName2 = $row[NameF];
$strLName2= $row[NameL];
}
?>
<hr>
<form class="formLayout">
<div class="formGroup">
<label>First name:</label>
<input name="nameF" type="text" autofocus value="<? echo $strFName2; ?>">
</div>
<div class="formGroup">
<label>Last name:</label>
<input name="nameL" type="text" value="<? echo $strLName2; ?>">
</div>
<div class="formGroup">
<label> </label>
<button>Submit</button>
<input type="hidden" name="updateID2" value="<? echo $updateID; ?>">
</div>
</form>
<?php
//Update
if (!empty($updateID2))
{
$sql = "UPDATE customertbl
SET NameL = '$strFName2', NameF ='$strLName2'
WHERE custID = '$updateID2' ";
mysqli_query($link, $sql) or die('Insert error: ' . mysqli_error($link));
}
//Insert
if (!empty($nameF) && !empty($nameL)) {
$sql = "Insert into customertbl (NameL, NameF)
VALUES ('$nameL', '$nameF')";
mysqli_query($link, $sql) or die('Insert error: ' . mysqli_error($link));
}
//Delete
if (!empty($deleteID)) {
$sql = "Delete from customertbl WHERE CustID= '$deleteID' ";
mysqli_query($link, $sql) or die('Delete error: ' . mysqli_error($link));
}
//List records
$sql = 'SELECT custID, NameF, NameL
FROM customertbl order by custID';
//$result is an array containing query results
$result = mysqli_query($link, $sql)
or die('SQL syntax error: ' . mysqli_error($link));
echo "<p>" . mysqli_num_rows($result) . " records in the database</p>";
?>
<table class="simpleTable">
<tr>
<th>Cust. ID</th>
<th>F. Name</th>
<th>L. Name</th>
<th>Delete</th>
<th>Update</th>
</tr>
<?php
// iterate through the retrieved records
while ($row = mysqli_fetch_array($result)) {
//Field names are case sensitive and must match
//the case used in sql statement
$custID = $row['custID'];
echo "<tr>
<td>$custID</td>
<td>$row[NameF]</td>
<td>$row[NameL]</td>
<td><a href='?deleteID=$custID'>Delete</a></td>
<td><a href='?updateID=$custID'>Update</a></td>
</tr>";
}
?>
</table>
</div>
</body>
</html>
有问题的代码块
//Update
if (!empty($updateID2))
{
$sql = "UPDATE customertbl
SET NameL = '$strFName2', NameF ='$strLName2'
WHERE custID = '$updateID2' ";
mysqli_query($link, $sql) or die('Insert error: ' . mysqli_error($link));
}
引用变量$strFName2和$strLName2，这些变量是仅按条件填充的变量。
//Populate Textbox
if (!empty($updateID)) {
$sql = "SELECT NameL, NameF
FROM customertbl
WHERE custID = '$updateID'";
mysqli_query($link, $sql) or die('Delete error: ' . mysqli_error($link));
$result = mysqli_query($link, $sql)
or die('SQL syntax error: ' . mysqli_error($link));
$row = mysqli_fetch_array($result);
$strFName2 = $row[NameF];
$strLName2= $row[NameL];
}
由于在UPDATE SQL 查询期间未定义变量$strFName2和$strLName2，因此您看不到所需的结果。
查询应引用$nameF和$nameL，因为这些变量始终是定义的（不包含在条件中），并且表单输入在其名称属性中使用nameF和nameL。
$sql = "UPDATE customertbl
SET NameL = '$nameF', NameF ='$nameL'
WHERE custID = '$updateID2';";
您还需要修复DELETE查询以引用列custID，而不是CustID因为您的架构似乎使用前者。
$sql = "Delete from customertbl WHERE custID= '$deleteID' ";