Delete multiple MySQL rows when boxes are Unchecked on PHP Form

346
January 21, 2017, at 2:28 PM

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.

Answer 1

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!

READ ALSO
PHP Variable Not Working in SQL Query [duplicate]

PHP Variable Not Working in SQL Query [duplicate]

This question already has an answer here:

276
PHP: Uploading images and removing the background color for transparency

PHP: Uploading images and removing the background color for transparency

Uploading an image with a solid white background, I'd like to dynamically remove it and have the background be transparentWhich works, except the white background is replaced by solid black

342
How to properly utilize PDO? [duplicate]

How to properly utilize PDO? [duplicate]

This question already has an answer here:

370
PHP Function only accepting array(“a”,“b”) as param and not variable that IS an array

PHP Function only accepting array(“a”,“b”) as param and not variable that IS an array

I'm a bit lost here, I am trying to call a function that will only accept an array parameter like this:

246