我遇到致命错误：在我编辑的 PHP 代码中未捕获 mysqli_SQL_exception

你好！我正在为学校项目制作一个简单的表格，我发现本教程很有帮助
“https://www.youtube.com/watch?v=NqP0-UkIQS4″>
PHP 和 MySQL 的 CRUD 操作：创建、读取、更新、删除 – YouTube
我遵循了一切，但是当涉及到更新/编辑我的表时，它总是会导致：

致命错误：未捕获 mysqli_sql_exception：您的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，了解在 ‘= `2023-03-22T11:50:28`, Number_Of_Items = `5`, Item = `Tempra`, Expiration_D…’ 行附近使用的正确语法1 在 C:\xampp\htdocs\Inven\edit.php:75 堆栈跟踪：#0 C:\xampp\htdocs\Inven\edit.php(75): mysqli_query(Object(mysqli), ‘UPDATE Clinic_i… ‘) #1 {main} 在第 75 行的 C:\xampp\htdocs\Inven\edit.php 中抛出。

抱歉英语不好，不是我的母语，谢谢。

我尝试过的：

query($sql);
	$row = $result->fetch_assoc();

	if (!$row) {
		header("location: /inven/index.php");
		exit;
	}


$Date_Received = $row["Date_Received"];
$Number_Of_Items = $row["Number_Of_Items"];
$Item = $row["Item"];
$Expiration_Date = $row["Expiration_Date"];
$Last_Updated = $row["Last_Updated"];
$Quantity = $row["Quantity"];

}
	else{

$id = $_POST["id"];
$Date_Received = $_POST["Date_Received"];
$Number_Of_Items = $_POST["Number_Of_Items"];
$Item = $_POST["Item"];
$Expiration_Date = $_POST["Expiration_Date"];
$Last_Updated = $_POST["Last_Updated"];
$Quantity = $_POST["Quantity"];

	do {
		if ( empty($id) || 
			empty($Date_Received) || 
			empty($Number_Of_Items) || 
			empty($Item) || 
			empty($Expiration_Date) || 
			empty($Last_Updated) || 
			empty($Quantity) ) 
			{ $errorMessage= "All the Fields are required";
				break;
		} 

		$sql = "UPDATE clinic_inventory" .
			"SET Date_Received = `$Date_Received`, Number_Of_Items = `$Number_Of_Items`, Item = `$Item`, Expiration_Date = `$Expiration_Date`, Last_Updated = `$Last_Updated`, Quantity = `$Quantity`" . "WHERE id = $id";
		
			$result = mysqli_query($connection, $sql);

			if (!$result) {
			$errorMessage = "Invalid Query: " . $connection->error;
			break;
		}

			$successMessage = "Item Successfully Added";
			header("location: /inven/index.php");
			exit;
			
}	while (false);
	
}


?>

解决方案1

在 SQL 中，字符串值应使用单引号，而不是反引号。另外，您的查询中的“SET”之前缺少一个空格，其内容为 – “UPDATE Clinic_inventorySET。您的 sql 应该如下所示 –

PHP

$sql = "UPDATE clinic_inventory SET " .
       "Date_Received = '$Date_Received', " .
       "Number_Of_Items = '$Number_Of_Items', " .
       "Item = '$Item', " .
       "Expiration_Date = '$Expiration_Date', " .
       "Last_Updated = '$Last_Updated', " .
       "Quantity = '$Quantity' " .
       "WHERE id = $id";

你也让自己对 SQL 注入敞开了大门，你应该使用准备好的语句和参数化查询 – 准备好的语句和存储过程 [^]

粗略估计，您的代码应该如下所示 –

PHP

$sql = "UPDATE clinic_inventory SET " .
       "Date_Received = ?, " .
       "Number_Of_Items = ?, " .
       "Item = ?, " .
       "Expiration_Date = ?, " .
       "Last_Updated = ?, " .
       "Quantity = ? " .
       "WHERE id = ?";

$stmt = $connection->prepare($sql);

if ($stmt) {
    $stmt->bind_param("ssssssi", $Date_Received, $Number_Of_Items, $Item, $Expiration_Date, $Last_Updated, $Quantity, $id);

    $stmt->execute();

    if ($stmt->affected_rows > 0) {
        //Your query was successful...
        $successMessage = "Item Successfully Added";
        header("location: /inven/index.php");
        exit;
    } else {
        //Handle error/s...
        $errorMessage = "Error updating item: " . $stmt->error;
    }

    $stmt->close();
} else {
    //Handle error/s...
    $errorMessage = "Error preparing statement: " . $connection->error;
}