If select option is unchecked, delete it from Database - Bootstrap Multiselect

Question

I have an html table with bootstrap-multiselect in one of the columns. I want the database to delete the role when the checkbox is unchecked.

I am listening to the changes using if (isset($_POST['roles_checkbox'])). Therefore, when the checkbox is unchecked, it never gets called and nothing happens. If the user has 2 roles and 1 gets unchecked it gets deleted. Nevertheless, if the user has 1 role it cannot be deleted. I want the users to be able to have no role assigned to them.

<?php
echo "
  <!--User Roles-->
  <td>
  <form method='post'>
    <input type='hidden' name='user_id' value=" . $row["user_id"] . ">"; ?>
    <select class='roles_checkbox' multiple='multiple' name="roles_checkbox[]" 
    onchange='this.form.submit()'>

      <?php $a = 1; ?>
      <?php foreach ($roles as $data):
        $new_sql = "SELECT role_id from users_roles, users WHERE users_roles.user_id = '" . 
        $row["user_id"] . "' AND users_roles.role_id = '" . $a . "' GROUP BY 
        users_roles.user_id";
        $checked_or_not = mysqli_query($connect, $new_sql);?>
          <option value="<?php echo $a ?>" <?php if ($checked_or_not->num_rows != 0) echo 
          "selected=\"selected\""; ?>><?php echo $data ?></option>
        <?php $a++; ?>
     <?php endforeach; ?>
   </select>

<?php
  echo "
  </form>
  </td>";

<!--Update User Role listenning to select box-->
if (isset($_POST['roles_checkbox'])) { // Use select name
    $user_id = $_POST['user_id']; // Use input name to get user id being modify

    // Start by deleting all the roles
    for ($i = 0; $i < $count; $i++) {
        $a = $i + 1;
        // Deleted all roles
        $query2 = "DELETE FROM users_roles WHERE user_id = '$user_id' AND role_id = '$a'";
        _log('$query2: ' . $query2);
        $in_ch2 = mysqli_query($connect, $query2);
    }

    foreach ($_POST['roles_checkbox'] as $selectedOption) {
        //echo $selectedOption . "\n";
        // Insert selected roles
        $query = "INSERT INTO users_roles(user_id, role_id) VALUES ('$user_id', '" . $selectedOption . "')";
        $in_ch = mysqli_query($connect, $query);
    }
    echo "<meta http-equiv='refresh' content='0'>";
    $connect->close();
}
?>

Answer 1

I think you're on the right path with your attempt at deleting all current user roles before reinserting the new ones. However, I think this is also where the issue lies.

Try instead of this:

// Start by deleting all the roles
for ($i = 0; $i < $count; $i++) {
    $a = $i + 1;
    // Deleted all roles
    $query2 = "DELETE FROM users_roles WHERE user_id = '$user_id' AND role_id = '$a'";
    _log('$query2: ' . $query2);
    $in_ch2 = mysqli_query($connect, $query2);
}

Doing this:

$delete_query = "DELETE FROM users_roles WHERE user_id = '$user_id'";
mysqli_query($connect, $delete_query);

---

EDIT based on comments

Knowing that user can have no roles, you would start be removing your initial input check if ($_POST['roles_checkbox']) and reqlacing it for a user id check i.e. if ($_POST['user_id']) this allows you to continue running the rest of your process without relying on roles input. See my updated example below...

// First check if the request includes a user id
$user_id = !empty($_POST['user_id']) ? (int)$_POST['user_id'] : null;
if ($user_id) {

    // Second, lets delete all existing roles
    $delete_query = "DELETE FROM users_roles WHERE user_id = '$user_id'";
    mysqli_query($connect, $delete_query);

    // Now we check if the request includes any selected role
    if (!empty($_POST['roles_checkbox'])) {

        // This array will hold MySQL insert values
        $insert_values = [];

        // Loop through request values, sanitizing and validating before add to our query
        foreach ($_POST['roles_checkbox'] as $role_id) {
            if ($role_id = (int)$role_id) {
                $insert_values[] = "('{$user_id}', '{$role_id}')";
            }
        }

        // Double check we have insert values before running query
        if (!empty($insert_values)) {
            $insert = "INSERT INTO users_roles(user_id, role_id) VALUES " . implode( ', ', $insert_values );
            mysqli_query($connect, $insert);
        }
    }

    echo '<meta http-equiv="refresh" content="0">';
    $connect->close();
}