PHP MySQL - SQL Query Help
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;
}
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.
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`)
Just added another query that checks if the user already exist in the selected role. It works fine.
精彩评论