MySQL表trending与status列默认值异常问题求助

我在MySQL中创建了名为categories的表，其中trending和status列的默认值均设置为0。但插入新记录时，无论是否勾选对应复选框，这两列的值都会被设为1。

前端表单代码 #

<div class="card card-outline card-info">  
   <div class="card-header">  
     <h4>  
        New Category
        <button type="button" class="btn btn-primary btn-md float-right" data-toggle="modal" data-target="#modal-default"> 
          ADD
        </button>
     </h4>
   </div>
   <div class="card-body"></div>
</div>

<div class="modal fade" id="modal-default">  
   <div class="modal-dialog">  
      <div class="modal-content">  
         <div class="modal-header">  
              <h4 class="modal-title">Add New Category</h4>  
              <button type="button" class="close" data-dismiss="modal" aria-label="Close">  
                  <span aria-hidden="true">&times;</span>
              </button>
         </div>
         <form action="code.php" method="POST" id="cat_form">  
             <div class="modal-body">  
                 <div class="form-group">  
                    <label for="">Category Name</label>  
                    <input type="text" name="name" class="form-control" required>  
                 </div>  
                 <div class="form-group">  
                    <label for="">Description</label>  
                    <textarea name="description" class="form-control" required rows="5">  
                    </textarea>  
                 </div>  
                 <div class="form-group">  
                    <label for="">Trending</label>  
                    <input type="checkbox" name="trending">Trending
                 </div>  
                 <div class="form-group">  
                    <label for="">Status</label>  
                    <input type="checkbox" name="status">Status 
                 </div>
             </div>  
             <div class="modal-footer justify-content-between">
                  <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>  
                  <button type="submit" name="category_save" class="btn btn-primary">Save</button>  
             </div>
         </form>
      </div>
   </div>
</div>

后端处理代码（原错误代码） #

if (isset($_POST['category_save']))  
{  
    $name = $_POST['name'];  
    $description = $_POST['description'];  
    $trending = $_POST['trending'] = true ? '1':'0';  
    $status = $_POST['status'] = true ? '1':'0';  
    
    $category_query = "INSERT INTO categories 
    (name,description,trending,status) VALUES 
    ('$name','$description','$trending','$status')"; 
    $cate_query_run = mysqli_query($con, $category_query);  
    if ($cate_query_run)  
    {  
       echo "<script>  
           alert(' - Category Inserted  Succesfully!'); 
           window.location.href='category.php';  
       </script>";  
    }  
    else  
    {  
        echo "<script>  
           alert(' - Category Insertion Failed!'); 
           window.location.href='category.php';  
        </script>";  
    }  
}

问题分析与解决方案 #

核心错误原因 #

1. 三元表达式逻辑错误：原代码中$trending = $_POST['trending'] = true ? '1':'0'; 是先执行$_POST['trending'] = true的赋值操作（永远返回true），再执行三元表达式，导致结果永远为'1'，完全忽略了复选框的实际状态。
2. 复选框未勾选时的字段缺失：HTML复选框未勾选时，表单不会提交该字段，直接访问$_POST['trending']或$_POST['status']会产生Undefined Index警告，同时无法正确识别未勾选状态。

修正步骤 #

1. 修正后端变量赋值逻辑

替换原代码中的trending和status赋值部分，改用isset()判断字段是否存在（即复选框是否被勾选）：

$name = $_POST['name'];  
$description = $_POST['description'];  
// 复选框勾选则为1，未勾选则为0
$trending = isset($_POST['trending']) ? '1' : '0';  
$status = isset($_POST['status']) ? '1' : '0';

2. 优化前端复选框（可选但更规范）

给复选框添加value="1"属性，明确勾选时提交的值：

<input type="checkbox" name="trending" value="1">Trending
<input type="checkbox" name="status" value="1">Status

3. 解决SQL注入风险（推荐）

原代码直接将用户输入拼接到SQL语句中，存在严重的SQL注入风险，建议使用MySQLi预处理语句：

if (isset($_POST['category_save']))  
{  
    $name = $_POST['name'];  
    $description = $_POST['description'];  
    $trending = isset($_POST['trending']) ? 1 : 0;  
    $status = isset($_POST['status']) ? 1 : 0;  
    
    // 使用预处理语句
    $stmt = mysqli_prepare($con, "INSERT INTO categories (name, description, trending, status) VALUES (?, ?, ?, ?)");
    mysqli_stmt_bind_param($stmt, "ssii", $name, $description, $trending, $status);
    
    if (mysqli_stmt_execute($stmt))  
    {  
       echo "<script>  
           alert('Category Inserted Successfully!'); 
           window.location.href='category.php';  
       </script>";  
    }  
    else  
    {  
        echo "<script>  
           alert('Category Insertion Failed!'); 
           window.location.href='category.php';  
        </script>";  
    }  
    mysqli_stmt_close($stmt);
}

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