How to create a custom table in WordPress and why

Screenshot of laptop showing phpMyAdmin of a WordPress database table

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.

10 responses to “How to create a custom table in WordPress and why”

  1. Esther Avatar

    Amazing post, thanks for sharing this article!

    I am following this course https://www.youtube.com/watch?v=SnB5lkcRRhA, but the steps of the manual installation are different. Any advice?

    Thank you

    1. Jacob Martella Avatar

      Hi Ester,
      This article isn’t telling you how to install WordPress. It assumes you’re comfortable with WordPress and PHP and know how to code. If you’re just trying to install WordPress, this blog article isn’t for you. Hope this helps!

      1. Esther Avatar

        Thanks for your time, Jacob!

        Do you mean database tables? Any recommendation to take a look?

  2. Dale Avatar
    Dale

    Very clear, thanks for this info.

    I was thinking it might be beneficial to use ‘IF NOT EXISTS’ in the create table statement. I guess you would only get an error message if the table is already there but I am not sure if an error message would cause issues at any point.

    Checkboxes below were not displaying correctly in firefox btw…

    1. Gee Avatar
      Gee

      I agree with Dale that “IF NOT EXISTS” should be added. In a plugin, it is normal to create database tables when activating but to NOT drop them during deactivation, only dropping tables when the plugin is unistalled. Therefore, if a user deactivates and then reactivates your sports_bench_create_db() function will be called again and the table will already existing.

      Another option is to put add all the required SQL commands to a string variable and use the dbDelta( $sql ) function to apply them. The advantage of this approach is that it copes with changes to database tables in later versions of your plugin. In the code, you simply update the CREATE statement(s) and dbDelta sorts out making the changes. You would also need a function to call dbDelta if your plugin database has changed because the plugin activation hook is not fired when the plugin is upgraded.

  3. Rihana Avatar
    Rihana

    Awww, the best way to create a WP custom table, thanks for your help.

  4. Fernando Bonato Avatar
    Fernando Bonato

    Hi Jacob, can I create a separate database to push and pull data from in wordpress? Say I want to create a full relational database for my marketing agency with tables like, clients, locations, billing, campaigns, etc.. Is it possible or I would have to go a more traditional route for that? I did something like this over 15 years ago using ASP.net. Thanks in advance for your help.

    1. Jacob Martella Avatar

      Hi Fernando,
      There probably is a way to do that, but it would probably be extremely hard to pull off. You would need a second set of database credentials to deal with, and I don’t know if you could do that with PHP. I think you’re just going to need to keep it all in the same database., and in fact that’s what I have for my stuff as well. It’s all just in the same database with a changed prefix.

  5. Vincent Duvernet Avatar

    Nice article 🙂 Where’s the next part (about filling datas) ?

    1. Jacob Martella Avatar

      Hey Vincent,
      Oops. Apparently long ago I forgot to go back and add the link to the next post. Anyway here it is: https://jacobmartella.com/2018/01/17/add-wordpress-admin-page-data-custom-table/

Leave a Reply

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