Creating Custom Tables in WordPress

If you are creating your own WordPress plugin, you may want to store data inside the WordPress database. There are a few options that you can use for this: You can create a custom settings page using the Settings API, save the data directly in the option table by using set_option() and get_option(), or create your own database tables and store your data in there.

In this tutorial, we are going to look at the process of how you will create custom tables in your WordPress plugin.

When you use a plugin that will use custom tables, you need to make sure that these tables will exist before you try to store data in them. To make sure that these tables exist, you should automatically create these tables when the plugin is activated.

WordPress has a function hook that you can use to run a function when the plugin is activated. It also comes with a function hook that will run a function when you deactivate the plugin. You can use both of these hooks to create and remove your custom tables in your plugin.

Create Table on Plugin Activation

To make sure that the tables exist when you activate the plugin, you need to use the hook register_activation_hook(), which takes two parameters: the file of the main plugin, and the function to run when the plugin is activated.

If you put this in the main plugin file, then you can use the following code:

register_activation_hook( __FILE__, 'pu_create_plugin_tables' );

function pu_create_plugin_tables()
{
    // enter code to create tables
}

When you create a new table in the database, it's important to keep to the coding standards of the other tables. When WordPress installs its tables, it will prefix the tables by default with wp_. You can change this inside the wp-config.php file. To make sure you use the correct database prefix, you can access this value inside the $wpdb object.

function pu_create_plugin_tables()
{
    global $wpdb;

    // current blog table prefix
    echo $wpdb->prefix . 'tablename';

    // multisite blog, original table prefix
    echo $wpdb->base_prefix . 'tablename';
]

The difference between the prefix and the base_prefix property is used when you have multisite enabled. If you use the $wpdb->prefix, you will get the prefix of the current site you are on. If you are on a multisite blog, this could return wp_2_. Using the $wpdb->base_prefix will return the prefix defined in the wp-config.php.

When you want to create the table, all you have to do is simply create the SQL for your table and store this in a variable to insert the dbDelta() function.

function pu_create_plugin_tables()
{
    global $wpdb;

    $table_name = $wpdb->prefix . 'tablename';

    $sql = "CREATE TABLE $table_name (
      id int(11) NOT NULL AUTO_INCREMENT,
      name varchar(255) DEFAULT NULL,
      UNIQUE KEY id (id)
    );";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );
}

The dbDelta() function will examine the current database and compare it with the table structure that will be created from your SQL. This means that if you are using SQL to create a new table and the table already exists, it will not run your SQL query.

The dbDelta() function isn't available at this stage of the application, so you will need to make sure you include the /wp-admin/includes/upgrade.php file before you call the dbDelta() function.

The next time you activate the plugin, it will run this function and the new table will be created in your database.

Adding Data to the Custom Table

To add data into your custom tables, again you use the $wpdb object. This has a method called insert(), which allows you to add data to your selected table.

The insert method has three parameters: the table name, an array of data to add to the table, and the format of the specified parameters.

function pu_insert_custom_table()
{
    global $wpdb;

    $wpdb->insert( 
        'table', 
        array( 
            'id' => 1, 
            'name' => 'John' 
        ), 
        array( 
            '%d', 
            '%s' 
        ) 
    );
}

Getting Data from the Custom Table

To retrieve the data from the custom database tables, you can again use the $wpdb object. There are two methods that you can use to get results out from a custom query: You can use $wpdb->get_row(), or use $wpdb->get_results().

The get_row() method will only return one row, so it should be used when you query the primary key of the table.

function pu_get_by_id( $id )
{
    global $wpdb;

    $table_name = $wpdb->prefix . 'tablename';

    $row = $wpdb->get_row( $wpdb->prepare('SELECT * FROM '.$table_name.' WHERE id = %d', $id) );

    return $row;
}

The get_results() method will return all rows that are returned from the query.

function pu_get_by_name( $name )
{
    global $wpdb;

    $table_name = $wpdb->prefix . 'tablename';

    $results = $wpdb->get_results( $wpdb->prepare('SELECT * FROM '.$table_name.' WHERE name = %s', $name) );

    return $results;
}