How To Properly Create Tables In WordPress Multisite Plugins

Recently, I added full WordPress Multisite compatibility to my Email Log WordPress plugin and during the process, I learned a lot about how to create and handle tables in WordPress Multisite plugins. I thought of documenting it here so that it would be useful for other plugin authors.

Creating tables in Single site installations

Before we get into WordPress Multisite, first let’s see how we can create a table in a single site WordPress installation. You have to hook into the register_activation_hook action, which will be called every time you activate your plugin and then check if your table exists or not. If it doesn’t exist, then you can create your table.

The following code shows how you can do it.

The above code will work even in WordPress Multisite installations, if your plugin will be activated individually for each blog. But if your plugin is networked activated, then the above code will create the table only for the primary blog.

Creating tables for all blogs in a WordPress Multisite installation

Now that we know how to create the table for a single blog, let’s see how we can create the table for all the blogs in a WordPress Multisite installation. Even in this case, we have to hook into the same register_activation_hook action, but should loop through all the blogs in the network.

The following code shows how you can do it.

Creating table when a new blog is created

The above code will create the table only for the blogs that were created before the plugin got network activated. We should also make sure that we create the table for every new that gets created. In order to do that we can hook into the wpmu_new_blog action.

The following code shows how you can do it.

Deleting the table when a blog is deleted

Now that we are creating the table for every new blog, it is our job to make sure that the table is deleted when the blog is deleted. In order to do that, we can hook into the wpmu_drop_tables filter.

The following code shows how you can do it.

Querying the correct table

When we are querying the table, we should always use $wpdb->prefix . $table_name. If we do that, then WordPress will automatically query the correct table based on the current blog. We don’t have to manually find out the blog id and add it to the table name.

Now your plugin should be completely compatible with WordPress Multisite πŸ™‚

Alternate approach

If you look into the above code closely, you will notice that we are creating one table for each blog in the network. For most WordPress Multisite installations, this shouldn’t be a problem. But some WordPress Multisite installation may have thousands and even hundreds of thousands of blogs. In those cases, we might end up creating huge amount tables which might become a bottleneck. Also my plugin needed just one table. Some plugins might need more than one table, which might also increase the number of tables that gets created.

One alternate approach is to create just one table for all blogs and then separate out data for each blog using a blog_id column. While querying the table, we can filter out based the blog_id column.

If I had started my plugin from scratch, I would have done that instead of creating separate tables.

Update: As Damian pointed out below in the comments, if you are using this approach, then you used useΒ $wpdb->base_prefix to get the main prefix and not the individual sites prefix.

Removing the tables when the plugin is deleted

The other thing to keep in mind is that we should delete all the tables when the plugin is deactivated and deleted. I will write a separate article explaining how we should do that.

With WordPress Multisite becoming more popular these days, I hope this information was useful to you. Do let me know if you have any question or comments. Also you can checkout the entire code of my Email Log plugin in github.

Related posts

Tags: , , ,

19 Comments so far

Follow up comments through RSS Feed | Post a comment

  • andygcook says:

    Thanks for putting this together, Sudar. My plugin, Leadin, creates three tables so I actually opted for implementing your column idea to filter by blog_id instead. It seems a bit cleaner than spinning up 3 x N tables for every site.

  • Ajay says:

    Hi Sudar,

    Thanks for the detailed tutorial. It’s very useful and I’m following this to make my plugin Contextual Related Posts multi-site compatible.

    You might want to update the code above to use restore_current_blog instead of switch_to_blog otherwise WP will think it is in a “switched” mode and can potentially return incorrect data.

  • friv says:

    Helpful articles for those new learn WordPress as I. Thank you

  • Damian says:

    Thanks for the article. I think that when you use the alternative approach you need to be sure to use in your queries $wpdb->base_prefix in order to get the main prefix and not the individual sites prefixes.

    Thanks for the info!

  • Gmail says:

    Thanks, However I am encountering problems with your RSS. I don’t understand why I am unable to join it. Is there anybody else getting the
    same RSS issues? Anyone who knows the solution will you kindly respond?

  • Esil says:

    Hi,
    Thanks a lot for your post. It helped me a lot to understand tables creation on a multisite in WordPress.
    I like the mention of $wpdb->base_prefix, too. I didn’t find it anywhere else, and it saved my life ! :p

  • Roberto says:

    I think we’ve made mistakes. if ($ wpdb-> get_v it ( “show tables like ‘{$ table_name}’)! = $ table_name) {

  • Hi,
    Great article. Helped me making my plugin multisite compatible.

    BTW, It seems that the following variable was not used afterwards. Isn’t it unnecessary?

    `$current_blog = $wpdb->blogid;`

    • Sudar says:

      Hello Afzal,

      Glad to know that the article was helpful to you.

      Yes, that line is not needed and I just removed it.

      Earlier I was manually switching back to the original blog and so I needed that. But later I changed it to use `restore_current_blog` function, so it is not needed any more.

      Good catch πŸ™‚

  • There is a special place in heaven for people who create tutorials like this that are so well documented and easy to follow and provide them for free. Seriously, you rock! Thank you so much, you made getting to the next step in creating my plugin much easier than I’d hoped πŸ™‚

    • Sudar says:

      This is the first comment I read today after waking up. Thanks for making my day brighter.

      Comments like this motivate me to write more tutorials. So thanks again πŸ™‚

  • Hotmail says:

    Hi. I like your method very much. Very good fixations. I hope you succeed.

  • Ivan Barreda says:

    Hi, in WP 5.2 is deprecated wpmu_new_blog

    The new code is

    function on_create_blog($params ) {
    if ( is_plugin_active_for_network( ‘plugin-name/plugin-name.php’ ) ) {
    switch_to_blog( $params->blog_id );
    create_table();
    restore_current_blog();
    }
    }
    add_action( ‘wp_insert_site’, ‘on_create_blog’);

    Change the hook wpmu_new_blog to wp_insert_site and the params that received are WP_Site object https://developer.wordpress.org/reference/classes/wp_site/

1 Trackbacks/Pingbacks so far

Leave a Reply

Your email address will not be published. Required fields are marked *