通过复选框与删除按钮删除数据库数据遇JSON格式错误等问题

问题描述 #

尝试通过复选框input的value属性存储ID，实现单条或多条数据库数据删除功能，但功能失效。此前修改删除方法解决过部分问题，重写delete.php后，请求payload出现val%5B%5D=339（即val[]=339），且收到响应{"readyState":4,"responseText":"","status":200,"statusText":"OK"}，无实际数据删除动作。

相关代码 #

删除函数（Post类） #

$query = "DELETE FROM `$this->table` WHERE id = ?";
$stmt = $this->conn->prepare($query);

$rows_deleted = 0;

foreach ($ids as $id) {
  $stmt->bindValue(1, $id, PDO::PARAM_INT);

  $stmt->execute();

  $rows_deleted += $stmt->rowCount();
}

return json_encode(['rows_deleted' => $rows_deleted]);

delete.php #

header('Access-Control-Allow-Origin: *');
header('Content-Type: application/x-www-form-urlencoded');
header('Access-Control-Allow-Methods: DELETE');

include_once '../../config/database.php';
include_once '../../models/post.php';

// 实例化数据库
$database = new Database();
$db = $database->connect();
$table = 'skandi';
$fields = [];

// 实例化Post对象
$product = new Post($db,$table,$fields);
$json = json_decode(file_get_contents("php://input"), true);

$product->id = $json['id'];

try {
  $response = $product->delete($product->id);
  echo $response;
} catch (\Throwable $e) {
  echo "Error occurred in delete method: " . $e->getMessage();
}

表格渲染代码 #

async function renderUser() {
    let users = await getUsers(); 
    let html = ``;

    users.forEach(user => {
        let htmlSegment = `
            <table class="box">
                <tr> 
                <th> <input type='checkbox' id='checkbox' name='checkbox[]' value=${user.id}> </th>                                           
                <td>  ${user.sku}</td>
                <td>  ${user.name}</td>
                <td>  ${user.price}</td>
                ${user.size ? `<td> Size: ${user.size} $ </td>` : ""} 
                ${user.weight ? `<td> Weight: ${user.weight}  Kg</td>` : "" }
                ${user.height ? `<td>  Height: ${user.height} CM</td>` : ""}
                ${user.length ? `<td>  Length: ${user.length} CM</td>` : ""}
                ${user.width ? `<td>  Width: ${user.width} CM</td>` : ""}
                </tr>
            </table>`;

            html += htmlSegment;
    });

    let container = document.querySelector('.message');
    container.innerHTML = html;
}
renderUser();

AJAX删除请求代码 #

$(document).ready(function () {
  $("#deleteBtn").click(function (e) {
    
    let checkboxes = document.querySelectorAll("input[type=checkbox]:checked");

    let ids = [];

    for (let checkbox of checkboxes) {
      ids.push(checkbox.value);
    }
    let data = {
      id: ids,
    };
    $.ajax({
      url: "/api/post/delete.php",
      type: "DELETE",
      data: JSON.stringify(data),
      dataType: "json",
      contentType: "application/json",
      success: function (response) {
        console.log(response);
      },
      error: function (error) {
        console.log(error);
      },
    });
  });
});

问题排查 #

1. 请求格式与后端解析不匹配：前端AJAX发送的是JSON格式数据（{"id": [339]}），但delete.php中设置的Content-Type为application/x-www-form-urlencoded，这会导致服务器无法正确识别请求体格式，进而json_decode无法解析到正确的ID数组。
2. 异常payload来源：出现val%5B%5D=339说明存在其他请求逻辑（或旧代码残留）发送了表单格式的请求，与当前AJAX的JSON发送逻辑冲突，需确认删除按钮仅绑定当前AJAX事件。
3. 后端参数校验缺失：未对$json['id']的存在性和类型进行校验，若解析失败会直接传递无效值给删除函数，导致无动作。

解决办法 #

1. 修正后端Content-Type设置 #

将delete.php中的header('Content-Type: application/x-www-form-urlencoded');替换为：

header('Content-Type: application/json');

2. 添加后端参数校验 #

在delete.php中解析JSON后添加校验逻辑：

$json = json_decode(file_get_contents("php://input"), true);

// 校验ID数组是否有效
if (!isset($json['id']) || !is_array($json['id']) || empty($json['id'])) {
    echo json_encode(['rows_deleted' => 0, 'error' => '请选择有效的删除项']);
    exit;
}

$product->id = $json['id'];

3. 优化删除函数（提升效率） #

将循环单条删除改为批量删除，减少数据库交互次数：

// 生成与ID数量匹配的占位符
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$query = "DELETE FROM `$this->table` WHERE id IN ($placeholders)";
$stmt = $this->conn->prepare($query);

// 绑定所有ID参数
foreach ($ids as $index => $id) {
    $stmt->bindValue($index + 1, $id, PDO::PARAM_INT);
}

$stmt->execute();
$rows_deleted = $stmt->rowCount();

return json_encode(['rows_deleted' => $rows_deleted]);

4. 前端添加空选择校验 #

在AJAX发送前检查是否选中了复选框：

$(document).ready(function () {
  $("#deleteBtn").click(function (e) {
    
    let checkboxes = document.querySelectorAll("input[type=checkbox]:checked");
    let ids = [];

    for (let checkbox of checkboxes) {
      ids.push(checkbox.value);
    }

    // 空选择校验
    if (ids.length === 0) {
        alert('请先选择要删除的项');
        return;
    }

    let data = {
      id: ids,
    };
    $.ajax({
      url: "/api/post/delete.php",
      type: "DELETE",
      data: JSON.stringify(data),
      dataType: "json",
      contentType: "application/json",
      success: function (response) {
        console.log(response);
        // 删除成功后重新渲染表格
        renderUser();
      },
      error: function (error) {
        console.log(error);
      },
    });
  });
});

5. 排查冲突请求 #

检查页面中是否存在其他绑定到#deleteBtn的点击事件，或其他发送删除请求的逻辑，确保只有当前AJAX代码触发删除操作。

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