Foreign key on update cascade with condition (or where value = value)

@AungHtetPaing__ , Ok.. but how to write the function which will block a group of users according to their group status ?

@NaderH logic is something like this.

function login_require_roleStatus($require_user_role_status){
     global $session;
     global $db;
     $current_user = current_user();
     $statement = $db->prepare(‘ select roles.role_status from users left join roles on users.role_id=roles.id where users.id=:id’);
     $result = $statement->execute([
         ‘:id’ => $current_user[‘id’]
    ]);
      //if Role status banned
     if ($result->role_status === '0'):
          $session->msg('d','banned ');
          redirect('home.php',false);
          //if user group allowed
      else:
        return true;
        endif;
     }

@AungHtetPaing__ , using sql query didn't help, it's all about the function that will use that query to process what I want.

@NaderH Sorry I don't understand what you mean. I already explained about possible solution.

@AungHtetPaing__ , I am sorry I didn't notice that you wrote the function, I wrote that sql query with your function

$sql = "SELECT user_role_status FROM users JOIN roles ON roles.role_level = users.user_level WHERE roles.role_status = '{$db->escape($user_role_status)}' LIMIT 1";

and I executed that query in my phpmyadmin and it worked, but I still when I change the status of a role from the roles table I got all the user_role_status column from the users changed then I got all the users blocked although not all roles in roles table blocked only the role I changed its status, so why all the user_role_status column in the users table change and block all users when I change only one role status from the roles table?

@AungHtetPaing__ , role_level and user_level are the int of the role or user level, Admin role = 1, Editor role = 2, User role = 3, So when the user_level is 1, it means he is an admin and so on, I am using it as a relation .. The issue is when I change the editor role status to be blocked in the roles table to block all editors in once, I got all users with all roles blocked because instead of changing only the usere with the editor role in the user_roles_status column in the users table it changes the whole column (it means all users), the whole column references to the value I changed not only the the cells with the editor role in that column.

@NaderH emm your query is not what I mean. please check my example query and function. You just need to get role status from roles table (nothing to do with users table) and check the role status and do what you want (block that user or something). All the users who have their role status block will be blocked.

user and role table relationship

• user belong to role (one user has one role)
• role has many user (role primary key id will be foreign key in users table)

I am not sure why it is so complicating or am I misunderstanding your question? I can’t explain better than this. Sorry :)

@AungHtetPaing__ , May be I can't explain well what exactly happens, shortly all functions and all sql queries are working well and they do what I want exactly, they are totally functional, the issue is when I change a value in the roles table it affects on a whole column in the users table not only the value which references to the one I changed.

// user_role_status in users table is a fk refers to role_status in roles table
// and role_status in roles table is a fk refers to id in statuses table.

statuses_table
id       is_active             
0         no                        
1         ok                        

roles_table
id       role              role_status
1        Admins                1
2        Editors               1
3        Users                 1

users_table
id       user_name       user_level       user_role_status
1        Admin             1                   1              
2        Editor1           2                   1               
3        User1             3                   1                
4        Editor2           2                   1               
5        User2             3                   1                
6        User3             3                   1                

I think this will make it more clear, when I change the status of the Editors (role_id='2') role in roles table to '0' to block any one with the user_level '2' (user_id=2,4) in the users table, I should got only the users with the id=2 and 4 became with a user_role_status = '0' but instead of that I got the whole column of the user_role_status changed to '0' and that means all users are blocked. that's the problem I have. and this is the sql query I use

ALTER TABLE users ADD CONSTRAINT fk_user_role_status FOREIGN KEY (user_role_status) REFERENCES roles(role_status) ON UPDATE CASCADE;
COMMIT;

I don't have any problem with the functions because they are already execute the blocking for the users whose user_role_status is '0'.

@NaderH

that I got the whole column of the user_role_status changed to '0'

that is because you are using 'on update cascade' with 'role_status' column and 'user_role_status' column. changing 'role_status' will update all of the 'user_role_status' column value. I already explained about that in my first reply and you are still doing that.

foreign key should link with primary key column of parent table (not other column basically) https://www.w3schools.com/sql/sql_foreignkey.asp.

@AungHtetPaing__ , Ok.. I will make user_role_status refer to the id of the roles table but how to make sure that it will be related to the role_status not to the role itself ( I mean the permission of the role), I will try that change and check what will happen.

@AungHtetPaing__ , Well, I tried what you said and now the whole user_role_status refers to the Admin role not to the user_role_status and If I changed the role_stataus for any role, nothing happens and no user_role_status will be blocked or changes to '0".

@AungHtetPaing__ , The problem here is that I can't make the role_status column a unique key because all of its values are duplicated ('1') by default.

@dkroft , No, How is that ??