php - 不要在 UPDATE 语句上使用空变量进行更新

Question

我正在尝试运行 SQL 更新，但当帖子变量为空时我不想更新。

我运行的代码是:

require '../includes/db.php';
$settings_owner = ( isset($_POST[wb_owner_field]) ? $_POST[wb_owner_field] : false );
$settings_title = ( isset($_POST[wb_title_field]) ? $_POST[wb_title_field] : false );
$settings_description = ( isset($_POST[wb_descr_field]) ? $_POST[wb_descr_field] : false );
$settings_keywords = ( isset($_POST[wb_keywd_field]) ? $_POST[wb_keywd_field] : false );
$settings_id = ( isset($_POST[wb_id]) ? $_POST[wb_id] : false );

try {
    $sql = "UPDATE Website  SET  website_owner = '$settings_owner', website_title = '$settings_title', website_description = '$settings_description', website_keywords = '$settings_keywords' WHERE  _ID = '$settings_id'  ";

    // Prepare statement
    $stmt = $conn->prepare($sql);

    // execute the query
    $stmt->execute();

    // echo a message to say the UPDATE succeeded
    echo $stmt->rowCount() . " records UPDATED successfully";
} catch (PDOException $e) {
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;

Answer 1

一种方法是测试 UPDATE 语句中的值:

$sql = "UPDATE Website  
    SET  website_owner = IF('$settings_owner' = '', website_owner, '$settings_owner'), 
        website_title = IF('$settings_title' = '', website_title, '$settings_title'), 
        website_description = IF('$settings_description' = '', website_description, '$settings_description'), 
        website_keywords = IF('$settings_keywords' = '',  website_keywords, '$settings_keywords')
    WHERE  _ID = '$settings_id'  ";

另一种方法是动态构建 UPDATE 语句。

$sets = array();
if ($settings_owner != '') {
    $sets[] = "website_owner = '$settings_owner'";
}
if ($settings_title != '') {
    $sets[] = "website_title = '$settings_title'";
}
...
if (!empty($sets)) {
    $sql = "UPDATE Website SET " . implode(', ', $sets) . " WHERE _ID = '$settings_id'";
    $stmt = $conn->prepare($sql);
    $stmt->execute();
}