PHP MySQL - SQL Query Help

Question

Hello, I have the following code to add users to a particular role. The code works fine but I'd like to change the SQL so it only inserts the user into a role if it doesn't already exist.

Any help is greatly appreciated. Here is my code:

    // ------------------------------------------------------------------
    // ADD SELECTED USERS TO SELECTED ROLE
    // ------------------------------------------------------------------
    if(isset($_POST['ddlAddSelected']) && $_POST['ddlAddSelected'] != 'Add To' && isset($_POST['checked']))
    {
        // get checked checkbox values - userId
        $checked = array_map('intval',$_POST['checked']);

        // get selected security role - role name
        $selected_role = mysqli_real_escape_string($conn, $_POST['ddlAddSelected']);

        // get role id from db
        $get_role_id = mysqli_query($conn, "SELECT RoleId, RoleName FROM roles WHERE RoleName = '$selected_role' Limit 1")
        or die($dataaccess_error);

        // if roleId present
        if(mysqli_num_rows($get_role_id) == 1)
        {
            $row = mysqli_fetch_array($get_role_id);
            $role_id = $row['RoleId'];
            $role_name = $row['RoleName'];

            $i=1;
            foreach($checked as $user_id)
            {
                // add selected users to role
                $add_selected = mysqli_query($conn, "INSERT INTO users_in_roles(UserId, RoleId, RoleName) VALUES($user_id, $role_id, '$role_name')")
                or die($dataaccess_error);

                $count = $i++;
            }

            // if sucess
            if($add_selected)
            {
                $msg = "<div class='msgBox3'>SUCCESS: ($count) USERS have been ADDED to ($selected_role) ROLE.</div>";
            }
            else
            {
                echo 'some error message here...';
            }
        }
    }
    elseif(isset($_POST['ddlAddSelected']) && $_POST['ddlAddSelected'] != 'Add To' && !isset($_POST['checked']))
    {
        $msg = $msg_error;
    }

Answer 1

Add unique key UserId + RoleID to the users_in_roles table.

Ps: why do you need RoleName field in users_in_roles table too?

CREATE UNIQUE INDEX `user_role` ON `users_in_roles` (`UserId`, `RoleID`)

Answer 2

Use:

INSERT INTO users_in_roles
SELECT $user_id, $role_id, '$role_name'
  FROM users_in_roles 
 WHERE NOT EXISTS(SELECT NULL
                    FROM users_in_roles
                   WHERE userid = $user_id
                     AND roleid = $role_id)

...to ensure that a role is inserted only if it doesn't already exist.

The SELECT will pick up the values from the variable -- it doesn't actually SELECT from the table in the FROM clause, that's only included to make the query valid.

Answer 3

Just added another query that checks if the user already exist in the selected role. It works fine.