Shared table across network

Question

Im currently creating a plugin that will read an xml file and import the data into 4 tables on a wordpress multisite. the tables are rather large, with ALOT of columns, so the post table is not suitable for this task. Every "blog" on the network needs to be able to access the data from this table.

I have tried creating the tables by following the codex instructions here using the dbDelta function: https://codex.wordpress.org/Creating_Tables_with_Plugins

but on a multisite this creates a set of tables for each "blog" in the network. This is redundant as the data would be identical across all the different table sets.

I have been googling around but can't really find a WordPress-way of doing this.

Does anyone have ideas as yo how this is best achieved? When the plugin is activated network-wide, it needs to create a single set of tables to be accessed from all the blogs in the network

Answer 1

Use $wpdb->base_prefix . 'table_name' as a table name when you want to create a table for the whole network, or when you want to run queries on it.

$wpdb->base_prefix is always the prefix for the current network’s main table.

Answer 2

You should create a table only on activation, if is not created, exist in the database. A small source example should help you.

The follow source create a table, also in single sites, maybe the plugin will activate in single mode for each side in the network.

register_activation_hook( __FILE__, 'on_activate' );
function on_activate() {

    create_table();
}

function create_table() {
    global $wpdb;

    $table_name = $wpdb->prefix . 'your_table_name';

    // Check, if exists
    if ( $wpdb->get_var( "show tables like '{$table_name}'" ) == $table_name ) {
        return NULL;
    }

    $sql = "CREATE TABLE " . $table_name . " (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        col VARCHAR(100) NOT NULL,
        PRIMARY KEY  (id)
    );";

    // // make dbDelta() available
    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
}

If your plugin is only for network activation, then add comment to the head blog of the plugin Network: TRUE. WP cheks this and allow the activation only networkwide. Also add in the source a check for Multisite, like if (function_exists('is_multisite') && is_multisite()).

But now the important part, is the value for the global $wpdb. To create a table in the globals namespace, not for an specific site use $wpdb->base_prefix, NOT $wpdb->prefix.