当前位置: 动力学知识库 > 问答 > 编程问答 >

Delete multiple MySQL rows when boxes are Unchecked on PHP Form

问题描述:

My current code seems to delete every row in the table.

Need to delete only the ones that are unchecked.

here is my code:

$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.");

}

}

This currently deletes every row, including the boxes that are checked (which are then added, but then deleted by the issue).

Need to add the checked, and remove any unchecked if they exist.

网友答案:

Created an array of all checked employees ID's.

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");
                            }
                    }
        }
        }

This code works great.. Any comments are welcome!

分享给朋友:
您可能感兴趣的文章:
随机阅读: