php - 当 PHP 表单上的复选框未选中时删除多个 MySQL 行

Question

我当前的代码似乎删除了表中的每一行。

只需删除那些未选中的。

这是我的代码:

$assigned_array = array($_POST['employee_assigned']);

        $assigned_employees = implode (",", $assigned_array);

// update unchecked assignments
        // get all employees and delete the ones not in the checked list
            $get_emp_sql = "SELECT * FROM `accounts` WHERE `permissions` > 0";
            $get_emp_result = mysqli_query($dblink, $get_emp_sql);
            while ($get_emp_row = mysqli_fetch_array($get_emp_result)) {
                // delete rows that are not checked
                    $delete_assignment = "DELETE FROM `provider_assignments` WHERE `provider_id`='".$pro_id."' AND '".$get_emp_row['id']."' NOT IN ('".$assigned_employees."')";
                    if (mysqli_query($dblink, $delete_assignment)) {

                    } else {
                        die ("Unassignment error.");
                    }
            }

目前，这会删除每一行，包括选中的框(然后添加，但随后被问题删除)。

需要添加选中的，并删除任何未选中的(如果存在)。

Answer 1

创建了所有已检查员工 ID 的数组。

if (strlen($_POST['employee_assigned']) > 0) {
        $assigned_employees = implode (",", $_POST['employee_assigned']);
        }

        // update unchecked assignments
        // get all employees and delete the ones not in the clicked list
            $get_emp_sql = "SELECT * FROM `accounts` WHERE `permissions` > 0";
            $get_emp_result = mysqli_query($dblink, $get_emp_sql);
            while ($get_emp_row = mysqli_fetch_array($get_emp_result)) {
                if (strlen($assigned_employees == 0)) {
                    // delete all rows for this provider
                        $delete_assignment = "DELETE FROM `provider_assignments` WHERE `provider_id`='".$pro_id."'";
                } else {
                    // delete rows that are not checked
                        $delete_assignment = "DELETE FROM `provider_assignments` WHERE `provider_id`='".$pro_id."' AND '".$get_emp_row['id']."' NOT IN ('".$assigned_employees."')";
                }
                    if (mysqli_query($dblink, $delete_assignment)) {

                    } else {
                        die ("Unassignment error.");
                    }
            }




    // update checked assignments
        if (strlen($_POST['employee_assigned']) > 0) {
        foreach ($_POST['employee_assigned'] as $assign_this_employee) {
            // check for existing assignment
                $check_exist = "SELECT * FROM `provider_assignments` WHERE `provider_id` = '".$pro_id."' AND `employee_id` = '".$assign_this_employee."'";
                $exist_result = mysqli_query($dblink, $check_exist);
                $exist_count = mysqli_num_rows ($exist_result);

                // if assignment doesn't exist, insert assignment
                    if ($exist_count == 0) {
                        // insert sql
                            $add_assignment_sql = "INSERT INTO `provider_assignments` (`provider_id`, `employee_id`) VALUES ('".$pro_id."', '".$assign_this_employee."')";
                            // execute sql
                            if (mysqli_query($dblink, $add_assignment_sql)) {
                            } else {
                                die ("assignment error");
                            }
                    }
        }
        }

这段代码效果很好。欢迎任何评论!