Duplicate meta_key and change prefix and then copy meta_value

Question

Duplicate meta_key _capabilities and change prefix from st_pr59t_capabilities to wp_capabilities and then copy meta_value of st_pr59t_capabilities to wp_capabilities

Take a look at this image: Phpmyadmin Image Illustration

I am working on creating wordpress sites SITE 1 (st_pr59t_) and SITE 2 (wp_) that will be accessed by the same set of users.

I have successfuly completed

1) SHARING USERS AND USER META between SITE 1 and SITE 2

2) SHARING LOGIN COOKIES between SITE 1 and SITE 2

So all is fine till here.

Now I want to sync not just users but also user roles (multiple user roles) of all users.

For this I tried the solution offered HERE:

function ksu_save_role( $user_id, $role ) {

    $prefix_1 = 'st_pr59t_'; // SITE 1's Table Prefix
    $prefix_2 = 'wp_'; // SITE 2's Table Prefix

    $caps = get_user_meta( $user_id, $prefix_1 . 'capabilities', true );
    $level = get_user_meta( $user_id, $prefix_1 . 'user_level', true );

    if ( $caps ){
        update_user_meta( $user_id, $prefix_2 . 'capabilities', $caps );
    }

    if ( $level ){
        update_user_meta( $user_id, $prefix_2 . 'user_level', $level );
    }
}
add_action( 'set_user_role', 'ksu_save_role', 10, 2 );

The above solution works great when only a single user role is assigned to a user. But if a user is assigned multiple user roles then it doesn’t work. I mean, it doesn’t sync all user roles.

After digging through the database I understood that the solution lies in cloning the "meta_value" of "st_pr59t_capabilities" to "wp_capabilities"

Is there a way to copy entire 'meta_value' for a 'user_id' from "st_pr59t__capabilities" to "wp_capabilities"?

Phpmyadmin Image Illustration

If we can copy the entire meta_value from "st_pr59t__capabilities" to "wp_capabilities" then all the user roles assigned to a user can be synced.

So what changes need to be done to the above mentioned code to achieve this?

Answer 1

You could try to use WPDB methods that will allow to clone user roles, this way:

add_action( 'set_user_role', 'ksu_save_role', 10, 2 );
function ksu_save_role( $user_id, $role ) {
    global $wpdb;

    $prefix1 = 'st_pr59t_';
    $prefix2 = 'wp_';

    ## --- USER LEVEL --- ##

    $level = get_user_meta( $user_id, $prefix1 . 'user_level', true );
    if( $level ) {
        update_user_meta( $user_id, $prefix2 . 'user_level', $level );
    }

    ## --- USER ROLES --- ##
    
    $caps = $wpdb->get_var( $wpdb->prepare( "
        SELECT meta_value FROM {$wpdb->prefix}usermeta
        WHERE meta_key = '{$prefix1}capabilities' AND user_id = %d
    ", $user_id ) );

    if( $caps ) {
        $wpdb->query( $wpdb->prepare( "
            UPDATE {$wpdb->prefix}usermeta as um SET meta_value = '%s'
            WHERE um.user_id = %d AND um.meta_key = '{$prefix2}capabilities'
        ", $caps, $user_id ) );
    }
}

Code goes in function.php file of your active child theme (or active theme). Tested and works. It should also work for you…

Edit: The correct way is to use add_user_role hook instead, replacing:

add_action( 'set_user_role', 'ksu_save_role', 10, 2 );

With

add_action( 'add_user_role', 'ksu_save_role', 10, 2 );