如何在PHP中实现：POST值非空时更新记录，为空时设默认值

Hey there! Let's get this update functionality working exactly how you need it. Your goal is to update the user table when POST values exist, and fall back to default values when they're empty—here's a corrected, robust implementation along with explanations of what was off in the original code.

First, Let's Address the Issues in the Original Code #

• Reserved Keyword Conflict: key is a reserved word in MySQL, so using it directly in your SQL will throw a syntax error. We need to wrap it in backticks `key`.
• Incorrect Logic: Your original SQL checks if the existing database key is not null, but we need to check if the POST value is present/non-empty instead.
• Unprocessed date Field: The original code doesn't handle empty POST values for date—we need to apply the same default value logic here.
• Missing Execution & Safety: The code cuts off before executing the query, and we should add type binding for better security.

Option 1: Handle Default Values in PHP (Flexible & Customizable) #

This approach lets you define default values directly in PHP, which is great if you need defaults that differ from the database's field settings.

<?php
// 1. Process POST parameters with default values
// Adjust defaults to match your actual requirements
$setKey = !empty($_POST['key']) ? $_POST['key'] : null; // Default to NULL if POST key is empty
$setDate = !empty($_POST['date']) ? $_POST['date'] : date('d/m/Y'); // Default to current date if POST date is empty

// 2. Prepare the SQL query (note the backticks around `key`)
$sql = "UPDATE user SET `key` = :key, `date` = :date WHERE id = :id";
$handler = $conn->prepare($sql);

// 3. Bind parameters with explicit types for safety
$handler->bindParam(':key', $setKey, PDO::PARAM_INT); // Use PARAM_STR if key is a string
$handler->bindParam(':date', $setDate, PDO::PARAM_STR);

// Replace $userId with your actual source of the user ID (e.g., from session or POST)
$userId = 100;
$handler->bindParam(':id', $userId, PDO::PARAM_INT);

// 4. Execute the query with error handling
try {
    $handler->execute();
    echo "User record updated successfully!";
} catch(PDOException $e) {
    die("Update failed: " . $e->getMessage());
}
?>

Option 2: Use Database Default Values (Strictly Follow Table Settings) #

If your user table already has default values defined for key and date, you can let MySQL handle the defaults directly using COALESCE():

<?php
// 1. Capture POST values (pass null if empty)
$setKey = !empty($_POST['key']) ? $_POST['key'] : null;
$setDate = !empty($_POST['date']) ? $_POST['date'] : null;

// 2. SQL query that falls back to database defaults
$sql = "UPDATE user 
        SET `key` = COALESCE(:key, DEFAULT(`key`)), 
            `date` = COALESCE(:date, DEFAULT(`date`)) 
        WHERE id = :id";
$handler = $conn->prepare($sql);

// 3. Bind parameters and execute
$handler->bindParam(':key', $setKey, PDO::PARAM_INT);
$handler->bindParam(':date', $setDate, PDO::PARAM_STR);

$userId = 100;
$handler->bindParam(':id', $userId, PDO::PARAM_INT);

try {
    $handler->execute();
    echo "User record updated successfully!";
} catch(PDOException $e) {
    die("Update failed: " . $e->getMessage());
}
?>

Key Notes to Remember #

• Always validate POST data before using it! For example, check if key is a valid integer or date is in the correct d/m/Y format to avoid storing invalid data.
• Ensure $conn is a properly initialized PDO connection (you should have already set this up with your database credentials).
• The id value should come from a trusted source (like a user session or authenticated API request) to avoid updating the wrong record.

内容的提问来源于stack exchange，提问作者Peter