Create a new wp data table based on wp_usermeta table-CodePudding

I need to copy certain data from wp_usermeta into a new table when a certain plugin is activated on a subsite for the first time. So far, I can get the $admin_ids to print as expected, but don't know how or if I can use these values in an IN statement in SQL. So far, the table is not created using the below methods.

Question is, how do I write this SQL query correctly so that when the function runs, a new table is created based on the wp_usermeta table where the current blog admins' IDs are equal to wp_usermeta.user_id.

I am not getting any errors, warnings, or notices when I run the function, wp simply stalls forever.

Here's my code.

function create_blog_usermeta_table(){
 if(function_exists('other_plugin_function')){
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();

     // WP_User_Query arguments
        $args = array(
            'role' => 'Administrator', 
            'fields' => 'ID' 
        );

        // The User Query
        $user_query = new WP_User_Query( $args );

        // Get the results
        $admins = $user_query->get_results();
        
        //Extract and format admin ids from the results
        $admin_ids = wp_list_pluck( $admins, 'ID', 'ID');
        $admin_ids = implode( ',', $admin_ids );

        
    $usermeta_table = "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}_usermeta AS
        SELECT *
        FROM wp_usermeta
        Where user_id IN ($admin_ids)";

    }

}

CodePudding user response：

This worked.

 //Create blog usermeta table containing usermeta for the current blog's users.
function create_blog_usermeta_table(){

   
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();

        // WP_User_Query arguments
        $args = array(
            'role' => 'Administrator', 
            'fields' => 'ID' 
        );

        // The User Query
        $user_query = new WP_User_Query( $args );

        // Get the results
        $query = $user_query->get_results();
        $admin_ids = wp_list_pluck( $query, 'ID', 'ID');
        $admin_ids = implode(',', $admin_ids);
        
        // Create new usermeta table for the blog users.
        $usermeta_table = "
        CREATE TABLE IF NOT EXISTS {$wpdb->prefix}usermeta AS
        SELECT *
        FROM wp_usermeta
        Where user_id IN ($admin_ids)";

require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta( $usermeta_table );
    }
register_activation_hook( MY_PLUGIN_PATH . '/my-plugin.php', 'create_blog_usermeta_table' );

CodePudding user response：

The previous solution did not copy indexes. This one does.

 //Create blog usermeta table containing usermeta for the current blog's users.
function create_blog_usermeta_table(){
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();

     // WP_User_Query arguments
        $args = array(
            'role' => 'Administrator', 
            'fields' => 'ID' 
        );

        // The User Query
        $user_query = new WP_User_Query( $args );

        // Get the results
        $admins = $user_query->get_results();
        $user_ids = wp_list_pluck( $admins, 'ID', 'ID');
        $user_ids = implode(',', $user_ids);
        $new_table = $wpdb->prefix . 'usermeta';
        // Create new usermeta table for the blog users.
        $usermeta_table ="CREATE TABLE  `{$wpdb->prefix}usermeta` LIKE wp_usermeta;
  
        INSERT `{$wpdb->prefix}usermeta` SELECT * FROM wp_usermeta WHERE user_id IN ($user_ids);";

    dbDelta( $usermeta_table );
    maybe_create_table( $wpdb->prefix . 'usermeta', $usermeta_table );
    }