A few tips on creating custom tables in WordPress plugins

Although it is recommended to avoid creating custom tables when you make WordPress plugins, however, there are some situations that it is more efficient to create your own custom tables. Fortunately, WordPress provides some great APIs that make our interaction with custom tables much easier. So here I’ve put together a few tips that I think should be considered when you deal with custom tables.

Create tables on plugin activation

First, we know we should create our custom database tables upon plugin activation. Therefore we need to know when the plugin gets activated to run our SQL query after. Fortunately, the answer is simple: register_activation_hook, which is the best solution. Usually, we use this function for all things related to the installation of our plugins, and custom tables are definitely one of those things.

So let’s create a sample plugin which is going to hold our codes in a better way. We’ll create a function to run after plugin activation.

Use table prefix and charset/collate variables

The next thing to consider is using the prefix variable. For some reasons such as better security or being able to have multiple WordPress installations in one database, WordPress allows us to set a custom prefix for database tables. It is controlled by a variable called prefix from $wpdb class which initially filled in wp-config.php by users.

Now you should put this variable at the beginning of your table name to let WordPress manage it as it does for other native tables.

In the following example, we use the prefix variable for our custom table name.

As you see in our above example, there is also another variable called $collate which contains the database collation/charset combination. As WordPress explains:

A character set is a collection of symbols (letters, numbers, punctuation, and special characters), when used together, represent meaningful words in a language. Computers use an encoding scheme so members of a character set are stored with a numeric value (e.g. 0=A, 1=B, 2=C, 3=D). In addition, a collation determines the order (i.e alphabetic) to use when sorting the character set.
WordPress.org

Database charset and collation are defined in wp-config.php and can be modified easily. However, what we need for creating our custom database tables is getting their values in a proper format and including them in our table creation query. It can be done by using $wpdb->get_charset_collate() which has been introduced in WordPress 3.5.

Here’s the behind the scenes:

bigint, id column data type

Another less important point is choosing right data types. Since WordPress uses bigint as the data type for any id column, it’s a good idea to use the same if you’re going to save one of WordPress’s id columns as an external key in your table. It will be more future-proof. However, it wouldn’t be a real problem in 99% of sites. But it’s good to be considered.

You may need to update your tables

From time to time, you may need to update your database table schema. For example, you may need to fix a wrong data type for a column or even add a new table to the database from a certain version of your plugin. So what is the best way to do it? should we use $wpdb and run CREAT or UPDATE queries? The answer is no!

Using dbDelta function, you can handle both table creation and schema updates. It’s a native WordPress function that does the hard thing in the background by comparing your new table schema with the installed one and applies the changes in the right way. It checks whether the table needs to be created or updated.

Here’s our example with dbDelta added:

Note that using dbDelta

  • You must load the function into your plugin as it’s not loaded by default.
  • If you change a field name, in a case of schema update, it will keep the previous one and make a new field with the new name.
  • In your SQL statement, you must keep each field on its own line.
  • You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
  • Your table must include at least one KEY and it must be defined with KEY keyword instead of INDEX.
  • You must not use any apostrophes or backticks around field names.
  • Field types must be all lowercase.
  • SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.
  • You must specify the length of all fields that accept a length parameter. int(11), for example.

Furthermore, it’s also a very good idea to record the database version in WordPress options table, so that we can use it in future. We may need to add a database update functionality or run a particular function for certain database versions.

Here’s our example containing version option

Don’t forget WPMU

As mentioned before, in a single-site WordPress installation, we use the register_activation_hook function to create our custom table which works as expected in Multisite too. The only problem is that our table will be created once for the network primary blog and other blogs won’t have their copy. So they don’t recognize the table, and our use will get a missing table error.

So to support multisite, first we should loop through all the existing blogs in the network and create the tables. The following code shows how you can do it.

As you see, we have slightly modified the previous code. We created a new function called “codeposter_activate”, and used that for register_activation_hook. When our plugin gets activated in this way, our new function will detect the network, loops through all the current blogs and creates an instance of our table for each blog. Also, it will work as usual for the single installation.

Next, we should add a piece of code that creates the table when a new blog is created. We need to hook into the wpmu_new_blog action.

So we have covered both new blogs and existing ones.

Remove your tables

When a blog is deleted in multisite, those tables that are created specifically for that blog should be removed because they are not functional anymore.

You can do it easily using the following filter hook:

We used a hook to remove our custom tables after removing a blog from Network. It is necessary because when you delete a blog from your network, your tables will remain useless forever as WordPress assign a unique table prefix for every blog and by removing a blog, its tables won’t be available for new blogs.

But in a single installation, it is a bit different. We don’t have a delete blog option. We can either deactivate a plugin or delete it. On plugin deactivation, you shouldn’t do anything regarding your custom table because It is so common that a user deactivates your plugin temporarily and activate it again. So don’t remove custom tables on plugin deactivation. It will kill your plugin!

The only situation that you can consider deleting your custom tables in a single WordPress installation is when the user decides to delete your plugin and remove all of its files. In this case, you can use uninstall.php file which is loaded when the user removes the plugin from WordPress admin panel. So you can query the database, drop your table(s), and let the database breathe better.

Here’s an example of how to do it:

Conclusion

In this tutorial we’ve looked at some tips you’ll need to consider when you’re creating custom database tables for your WordPress plugins. We created a sample plugin that puts together all things discussed in this article. You can find the code of the plugin below.

 

and uninstall.php

 

Leave a Reply

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