带有 if else 条件的 SQL UPDATE

Question

如果表中的相应列不为空，我想更新列post_author和 变量。post_user如果要取消这些特定条件并只留下一个，例如post_author，那么一切正常，我只有 sql 'case' 部分有问题。

有这个问题：

Notice: Fail 你的SQL语法有错误；检查与您的 MariaDB 服务器版本对应的手册，了解在第 1 行的 'SET post_author = case when post_author !=null then 'evgen' else null end, SET p' 附近使用的正确语法

$query = "UPDATE posts SET post_title = '{$post_title}', 

post_tags= '{$post_tags}', 

post_date = now(), 

post_image = '{$post_image}', 

post_content = '{$post_content}',  

post_status = '{$post_status}', 

post_category_id = '{$post_category_id}', 

SET post_author = case when post_author !=null then '{$post_author}' 

                                               else null end, 

SET post_user = case when post_user !=null then '{$post_user}' 

                                           else null end 

WHERE post_id = $the_great_post_id ";

我有这个 HTML：

<?php  

if(null !=  $post_user) {

?>

      <div class="form-group">

        <label for="post_user">User</label>

       <input type="text" class="form-control" name="post_user" value="<?php echo $post_user; ?>">

    </div>

<?php   }

if(null !=  $post_author) {

?>

      <div class="form-group">

        <label for="post_author">Author</label>

       <input type="text" class="form-control" name="post_author" value="<?php echo $post_author; ?>">

       </div>

<?php   }  ?>

Answer 1

您不需要SET最后两个值&nbsp; $query = "&nbsp; &nbsp; &nbsp; UPDATE posts&nbsp;&nbsp; &nbsp; &nbsp; SET post_title = '{$post_title}',&nbsp;&nbsp; &nbsp; &nbsp; post_tags= '{$post_tags}',&nbsp;&nbsp; &nbsp; &nbsp; post_date = now(),&nbsp;&nbsp; &nbsp; &nbsp; post_image = '{$post_image}',&nbsp; &nbsp; &nbsp; post_content = '{$post_content}',&nbsp;&nbsp; &nbsp; &nbsp; post_status = '{$post_status}',&nbsp;&nbsp; &nbsp; &nbsp; post_category_id = '{$post_category_id}',&nbsp;&nbsp; &nbsp; &nbsp; post_author = case when post_author !=null then '{$post_author}' else null end,&nbsp;&nbsp; &nbsp; &nbsp; post_user = case when post_user !=null then '{$post_user}' else null end&nbsp;&nbsp; &nbsp; &nbsp; WHERE post_id = $the_great_post_id ";无论如何，你不应该在 SQL 中使用 PHP 变量，这样做会使你面临 SQL 注入的风险。为避免这种情况，您应该查看 PHP 数据库驱动程序的准备语句和绑定参数。

Answer 2

当你有SET post_author = case when post_author !=null then '{$post_author}'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;else null end,有几个问题，首先你不需要SET. 其次，usingelse null会将值设置为 null 而不是保留字段的原始值。在这个版本中，它使用...post_author = case when post_author is null then '{$post_author}' else post_author end,&nbsp;放在一起给你...UPDATE posts&nbsp;&nbsp; &nbsp; SET post_title = '{$post_title}',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; post_tags= '{$post_tags}',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; post_date = now(),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; post_image = '{$post_image}',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; post_content = '{$post_content}',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; post_status = '{$post_status}',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; post_category_id = '{$post_category_id}',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; post_author = case when post_author is null then '{$post_author}' else post_author end,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; post_user = case when post_user is null then '{$post_user}' else post_user end&nbsp;&nbsp; &nbsp; WHERE post_id = $the_great_post_id&nbsp;另一件需要指出的事情是，您应该使用准备好的语句，因为这是不安全的，并且可能会出现各种问题。