PHP更新查询异常：数据被清空而非更新

Hey there! Let's figure out why your plant entry is getting wiped clean when you hit the update button—there are a few straightforward issues in your code that are causing this. Let's break them down step by step:

1. Mismatched Form Field Names & PHP $_POST Variables #

Your form uses input names like botanicName and commonName, but in your update script, you're trying to pull values using $_POST['bot_name'] and $_POST['comm_name']—these don't match at all! Since the form never sends data under those bot_name/comm_name keys, those $_POST calls return empty values, which then get written to your database, clearing the fields.

2. Wrong Variables in mysqli_real_escape_string #

After you assign the (incorrect) $_POST values to variables like $botanicName, you then pass non-existent variables like $bot_name to mysqli_real_escape_string. This means you're escaping a variable that doesn't hold any form data, resulting in empty strings being saved.

3. Typo in the "Common Use" Form Field #

In your form, the Common Use input has value="<?=$m_height?>" instead of value="<?=$comm_use?>". So even before submission, this field is filled with the height value (or empty if that variable was messed up), leading to wrong data being sent.

Fixed Code Snippets #

Let's fix these issues one by one:

Updated update_plant.php Script

<?php 
// MySQL Database Connect 
require_once("connect.php"); 

// Read values from form (match input "name" attributes exactly!)
$botanicName = $_POST['botanicName']; 
$commonName = $_POST['commonName']; 
$plantDescription = $_POST['plantDescription']; 
$commonUse = $_POST['commonUse']; 
$maxHeight = $_POST['maxHeight']; 
$maxWidth = $_POST['maxWidth']; 
$popular = $_POST['popular']; 

// Escape variables with the CORRECT assigned variables
$botanicName = mysqli_real_escape_string($conn, $botanicName); 
$commonName = mysqli_real_escape_string($conn, $commonName); 
$plantDescription = mysqli_real_escape_string($conn, $plantDescription); 
$commonUse = mysqli_real_escape_string($conn, $commonUse); 
$maxHeight = mysqli_real_escape_string($conn, $maxHeight); 
$maxWidth = mysqli_real_escape_string($conn, $maxWidth); 
$popular = mysqli_real_escape_string($conn, $popular); 

// Update query (unchanged logic, now uses valid variables)
$query="UPDATE plant SET botanicName='$botanicName', commonName='$commonName', plantDescription='$plantDescription', commonUse='$commonUse', maxHeight='$maxHeight', maxWidth='$maxWidth', popular='$popular' WHERE plantID='2'"; 

// Execute query and handle errors
$results = mysqli_query($conn, $query ); 
if(!$results) { 
 echo ("Query error: " . mysqli_error($conn)); 
 exit; 
} else { 
 header("location: ../make_changes.html"); 
} 
?>

Fixed Form Section

<h2>Edit a Plant</h2>

<?php 
// Fetch existing plant data
$query = "SELECT * FROM plant WHERE plantID='2'"; 
$results = mysqli_query($conn, $query ); 
if(!$results) { 
 echo ("Query error: " . mysqli_error($conn)); 
} else { 
 while ($row = mysqli_fetch_array($results)) { 
 $bot_name = $row['botanicName']; 
 $comm_name = $row['commonName']; 
 $pl_desc = $row['plantDescription']; 
 $comm_use = $row['commonUse']; 
 $m_height = $row['maxHeight']; 
 $m_width = $row['maxWidth']; 
 $pop = $row['popular']; 
 } 
} 
?>

<form method="post" action="code/update_plant.php">
<p>Botanic Name: <input type="text" name="botanicName" value="<?=$bot_name?>" required></p>
<p>Common Name: <input type="text" name="commonName" value="<?=$comm_name?>" required></p>
<p>Plant Description: <input type="text" name="plantDescription" value="<?=$pl_desc?>" required></p>
<!-- Fixed the value here to use $comm_use instead of $m_height -->
<p>Common Use: <input type="text" name="commonName" value="<?=$comm_use?>" required></p>
<p>Max. Height (m): <input type="text" name="maxHeight" value="<?=$m_height?>" required></p>
<p>Max. Width (m): <input type="text" name="maxWidth" value="<?=$m_width?>" required></p>
<p>Popular? (Y/N): <input type="text" name="popular" value="<?=$pop?>" required></p>
<input type="submit" name="submit" value= "Update">
</form>

Quick Security Upgrade: Use Prepared Statements #

Instead of relying on mysqli_real_escape_string, prepared statements are a safer way to avoid SQL injection and variable mix-ups. Here's how you could rewrite the update logic:

// Prepare the statement (placeholders instead of direct variables)
$stmt = $conn->prepare("UPDATE plant SET botanicName=?, commonName=?, plantDescription=?, commonUse=?, maxHeight=?, maxWidth=?, popular=? WHERE plantID=2");
// Bind parameters (use "s" for strings, "i" for numbers if needed)
$stmt->bind_param("sssssss", $botanicName, $commonName, $plantDescription, $commonUse, $maxHeight, $maxWidth, $popular);
// Execute the update
$stmt->execute();

if ($stmt->affected_rows === 0) {
    echo "No record updated—double-check if plantID=2 exists!";
} else {
    header("location: ../make_changes.html");
}
$stmt->close();

内容的提问来源于stack exchange，提问作者T.Gowing