How to create a custom table in WordPress and why

WordPress comes with a number of basic tables created in your site’s database. There are tables for posts and their meta data, users and their meta data, site options and more.

And for the most part those work just fine. Even if you have a slightly complex website, you can use custom post types and custom fields to accomplish the job.

But sometimes those tables aren’t quite enough. Sometimes you need to create custom so that you have more control. And that’s where custom tables come into play.

Why you might need to create a custom table

So the first question you might have is “In what situation would I need to have a custom table for my website?” And that’s a great question.

The default WordPress tables work just fine for the vast majority of what you want to do, whether you’re just a company, a blogger or a developer working on building websites. Between the custom post types and custom fields (ACF, Pods, etc.), you can design a front-end system that works for you.

But if you’re building a project that deals with custom data, then it’s probably best to at least thinking about whether the data would be better off in a custom table. Gravity Forms has custom tables, and Easy Digital Downloads is going to be making the switch over to custom tables in 2018 as well. And Sports Bench is built with custom tables.

That’s because it just works better. You’re not having to sort through thousands of post meta rows to get just a couple of bits of post meta data. Plus, you know the structure of the table(s) which makes it easier to write code to get the data.

So if you’re looking to build a complicated products, custom tables are likely the way to go.

How to create a custom table

This tutorial will focus primarily on how to get the MySQL to work the WordPress way to create a custom table in your WordPress database. If you’re not familiar with MySQL, I would highly recommend you take a look at the section on it on W3Schools before you go any further.

So our MySQL statement to create a table is super simple. For this tutorial, we’re going to create a small table for sports teams with the team id, team name, team location (city and state) and the name of the team’s stadium. So in MySQL, our statement would look like this.

CREATE TABLE wp_sb_teams(
 team_id INTEGER NOT NULL AUTO_INCREMENT,
 team_name TEXT NOT NULL,
 team_city TEXT NOT NULL,
 team_state TEXT NOT NULL,
 team_stadium TEXT NOT NULL,
 PRIMARY KEY (team_id))

That’s all the MySQL that we’re going to write. Now to write this in the WordPress way.

All of the following code will go in your functions.php file or your main plugin file. We’ll first need to call the $wpdb global object so that we can communicate with the database, and then we get the charset_collate and then bring in the upgrade.php file included in WordPress. Don’t worry too much about the last two items. These are things that will just make our process easier.

Next we need to name the table. It’s usually best to use the same table prefix as the rest of the site, so put the $wpdb->prefix before the rest of the table name. Next, we’ll put our MySQL statement inside of a variable, as well as the charset_collate from earlier.

And finally, we run that variable through the dbDelta function, which will run our MySQL to create the table. So, our final code will look like this.

function sports_bench_create_db() {
 global $wpdb;
 $charset_collate = $wpdb->get_charset_collate();
 require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

 //* Create the teams table
 $table_name = $wpdb->prefix . 'sb_teams';
 $sql = "CREATE TABLE $table_name (
 team_id INTEGER NOT NULL AUTO_INCREMENT,
 team_name TEXT NOT NULL,
 team_city TEXT NOT NULL,
 team_state TEXT NOT NULL,
 team_stadium TEXT NOT NULL,
 PRIMARY KEY (team_id)
 ) $charset_collate;";
 dbDelta( $sql );
}
register_activation_hook( __FILE__, 'sports_bench_create_db' );

You’ll note that I have this wrapped inside a function which is called with the register_activation hook in WordPress. This makes sure this code is called when we activate this plugin. If you just write this code out in the open, it’s going to run every single time you load a page, and that will cause load issues if not errors. And besides, you’ll only need to run this code once usually.

Where you can go from here

So, what can you do with custom tables? Well, just about anything, although that’ probably not a great approach.

But it does give you another solution to fix a problem. You can use this when you’re creating a product for the WordPress market that deals with a lot of data that’s related to each other, like a sports plugin or an e-commerce plugin. 

But really, the deciding factor between whether you should or should not use a custom table comes down to whether or not it’s going to make the site better or not. I can give you an answer for every site because every site is different. That’s your decision to make.

And next time we’ll talk about how you can add data to that table through the admin.