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.
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.
Glad to know that my article was helpful to you.
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.
Thanks for pointing it out. I have fixed it in the gist and should reflect in the blog post once the cache clears.
Helpful articles for those new learn WordPress as I. Thank you
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!
Thanks for pointing it out. I have updated the blog post and added a small note about it.
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?
I am not sure if I fully understand what you mean by RSS issue.
Can you explain it a bit more?
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
Glad to know that you found the article useful.
I think we’ve made mistakes. if ($ wpdb-> get_v it ( “show tables like ‘{$ table_name}’)! = $ table_name) {
Hello Roberto,
Can you give me more information about what you think that is wrong?
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;`
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 π
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 π
Hi. I like your method very much. Very good fixations. I hope you succeed.
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/