Create a custom table plugin in WordPress

For WordPress, you can create a custom post type, custom taxonomy, and custom category that fit your need. But sometimes you may need your own table structure to store your data. For example, you synchronize data from an external system into WordPress. A custom table will be the best choice since you can manage the table structure to match the external system easily. Today I gonna share with you how to create a simple custom table via the plugin. You can create a custom table in functions.php. However, I prefer building the custom table via a plugin that allows me to manage the table structure and versioning easier.

Create your own plugin

In WordPress, create a new custom-table folder under the plugins folder. Then create a new custom-table.php which is your main plugin file.

The path is “wp-content>plugins>custom-table>custom-table.php“. Then add the code below into your custom-table.php.

/**
 * Plugin Name:       Create a custom table in WordPress
 * Plugin URI:        
 * Description:       Create a custom table in WordPress
 * Version:           1.0
 * Requires at least: 5.0
 * Requires PHP:      7.4.0
 * Author:            Apple Rinquest
 * Author URI:        https://applerinquest.com/
 * License:           GPL v2 or later
 * License URI:       https://www.gnu.org/licenses/gpl-2.0.html
 * Text Domain:       custom-table-plugin
 * Domain Path:       /languages
 */

We added the plugin name at the header in our main plugin file, so the plugin will appear on our Plugins page on the dashboard.

Create a database table

We will create a PHP function called “ar_customtable_install“. In this function, we will create the database table name “tbl_jobs“. The sample PHP code is below.

function ar_customtable_install() {
    // $wpdb uses to interact with a database in WordPress
    global $wpdb;
    // we will use the same table prefix which is set in wp-config.php
    $table_name = $wpdb->prefix . 'tbl_jobs';

    // adding SQL statement here
}

A wpdb class

We use a wpdb class to interact with a database in WordPress. If you want to interact with the custom table in WordPress, you will use the wpdb class. If you want to interact with the standard WordPress tables (post type, taxonomy, and category), you will use a wp_query class.

$wpdb->prefix

In WordPress, you can define a database table prefix in wp-config.php. By default, the prefix is “wp_“. If you want to create a custom table that starts with the same WordPress prefix, you can use the $wpdb->prefix.

Creating our table

As you know you can create the table using an SQL query directly in phpMyAdmin or other MySQL management tools (SQLyog or MySQL WorkBench). However, if you create the plugin for selling or sharing with other people, it is not convenient for them.

dbDelta function

In WordPress, we will use the dbDelta function which is located in “wp-admin/includes/upgrade.php ” to create the table and update the table structure in MySQL or MariaDB for us.

The dbDelta function will check the current table structure in MySQL and compare it to the desired table structure in the code. If the dbDelta doesn’t see the desired table in MySQL, the dbDelta will create the new desired table in MySQL. If the dbDelta sees the difference between the current table and the desired table, the dbDeleta will update the current table structure following the desired table structure. In short, the dbDelta function will update the table structure for you by updating the existing create table command.

To work with dbDelta, you must follow the rules below when you write the code.

• You must put each field on its own line in your SQL statement
• You must have two spaces between the PRIMARY KEY word and the definition of your primary key
• You must use the KEY word rather than INDEX word
• You must include at least one KEY
• KEY must be followed by a single space and then a key name. Then add a space and open the parenthesis with the field name
• You must not use any apostrophes or backticks around field names
• Field types must be lowercase
• SQL keywords must be uppercase such as CREATE, UPDATE, etc
• You must specify the length of all fields if the field type accepts the length parameter, for example, INT(11)

Below is an example of an SQL statement regarding the rules.

function ar_customtable_install()
{
  global $wpdb;

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

  // ### SQL Statement starts
  // we set the default character set and collation for the table to avoid the "?" conversion.
  // https://developer.wordpress.org/reference/classes/wpdb/get_charset_collate/
  $charset_collate = $wpdb->get_charset_collate();

  $sql = "CREATE TABLE $table_name (
  id mediumint(9) NOT NULL AUTO_INCREMENT,
  expire_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  job_title tinytext NOT NULL,
  job_desc text NOT NULL,
  url varchar(55) DEFAULT '' NOT NULL,
  PRIMARY KEY  (id)
) $charset_collate;";

  // To use the dbDelta class, we have to load this file, as it is not loaded by default
  require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

  // call the class
  dbDelta($sql);
  // ### SQL Statement ends
}

Adding the initial data – optional

Sometimes you may need to add the initial data in your custom table. Below is an example of how to achieve that.

function ar_customtable_install_data()
{
  global $wpdb;

  // an initial data
  $expire_date = strtotime('+15 days', date('Y-m-d'));
  $job_title = 'Backend Developer';
  $job_desc = 'developing and maintain the web applications and websites';
  $url = 'https://example.com/backend-dev-12345';

  // using the same table prefix in wp-config.php
  $table_name = $wpdb->prefix . 'tbl_jobs';

  // insert the initial data
  $wpdb->insert(
    $table_name,
    array(
      'expire_date' => $expire_date,
      'job_title' => $job_title,
      'job_desc' => $job_desc,
      'url' => $url
    )
  );
}

A complex SQL queries and SQL injection

Sometimes you may need to create complex SQL queries. You can use the $wpdb->query() and $wpdb->prepare() to achieve your goal. The $wpdb->query() will execute any SQL statement and the $wpdb->prepare() will be used to protect the queries against SQL injection attacks in WordPress.

Versioning for updating the table structure

Versioning is very important for updating the table structure in WordPress. We use this wp_options table for recording our version number of the table structure. Here is a sample of the code.

add_option( "ar_db_version", "1.0" );

In general, developers will commit the code to GitHub or other source code control tools. They will add the message along with the committed code. In my case, I commit the changes to GitHub. So I don’t add any changes log to WP when the custom table is changed. However, you can create the change log table (another custom table) to store the changes you make regarding the table structure if you prefer.

Combine the figure A, B, C and D

Now we understand each piece of code I mentioned above. It is time to combine them together.

<?php
/**
 * Plugin Name:       Create a custom table in WordPress
 * Plugin URI:        
 * Description:       Create a custom table in WordPress
 * Version:           1.0
 * Requires at least: 5.0
 * Requires PHP:      7.4.0
 * Author:            Apple Rinquest
 * Author URI:        https://applerinquest.com/
 * License:           GPL v2 or later
 * License URI:       https://www.gnu.org/licenses/gpl-2.0.html
 * Text Domain:       custom-table-plugin
 * Domain Path:       /languages
 */

// # our databse table version
global $ar_db_version;
$ar_db_version = '1.0';


/**
 * create a database table
 */
function ar_customtable_install()
{
  global $wpdb;
  global $ar_db_version;

  // # we use the same table prefix in wp-config.php
  $table_name = $wpdb->prefix . 'tbl_jobs';



  // # we set the default character set and collation for the table to avoid the "?" conversion.
  $charset_collate = $wpdb->get_charset_collate();

  // # we add the SQL statement for creating a database table
  // The SQl statement must follow the rules of dbDelta function
  $sql = "CREATE TABLE $table_name (
          id mediumint(9) NOT NULL AUTO_INCREMENT,
          expire_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
          job_title tinytext NOT NULL,
          job_desc text NOT NULL,
          url varchar(55) DEFAULT '' NOT NULL,
          PRIMARY KEY  (id)
        ) $charset_collate;";

  // # To use the dbDelta class, we have to load this file, as it is not loaded by default
  require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

  // # call the dbDelta class
  dbDelta($sql);



  // # insert our database table version into the wp_options table via add_option function
  add_option('ar_db_version', $ar_db_version);
}



/**
 * adding the initial data (optional)
 */
function ar_customtable_install_data()
{
  global $wpdb;

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


  // # our initial data
  $expire_date = strtotime('+15 days', date('Y-m-d'));
  $job_title = 'Backend Developer';
  $job_desc = 'developing and maintain the web applications and websites';
  $url = 'https://example.com/backend-dev-12345';

  // # insert our initial data into the database table
  $wpdb->insert(
    $table_name,
    array(
      'expire_date' => $expire_date,
      'job_title' => $job_title,
      'job_desc' => $job_desc,
      'url' => $url
    )
  );
}

Calling the functions in the plugin

Now we want to call the PHP functions above when the plugin is activated. To do that, we will use the register_activation_hook action hook.

At the main file of your plugin (in our case, the main plugin file is custom-table.php), add the code below in that main file.

register_activation_hook( __FILE__, 'ar_customtable_install' );
register_activation_hook( __FILE__, 'ar_customtable_install_data' );

Now in the WordPress admin panel, activate your custom table plugin then check the database via PHPMyAdmin and wp_options table. You should see the new database table and new option names “ar_db_version”. See the screenshot below.

Great! if you see the same result as mine, your code is working. Next, we will add the upgrade function in order to update the database table structure.

Adding an upgrade function (update code and use versioning)

After you use your database table for a while, it may be a need that you want to upgrade the table structure. To do that, we will update some code in the ar_customtable_install function and use the “ar_db_version” option to activate the upgrade.

Let’s say, we want to extend the URL field length from 55 to 150. You will override the ar_customtable_install function below.

<?php

// # our database table version
global $ar_db_version;
// original version is 1.0
$ar_db_version = '1.1'; // you have to manually change the version number from 1.0 to any versioning you like. in this case, I change from 1.0 to 1.1

/**
 * create a database table
 */
function ar_customtable_install()
{
    global $wpdb;
    global $ar_db_version;
    $installed_version = get_option("ar_db_version"); // use for upgrade table structure

    // # we use the same table prefix in wp-config.php
    $table_name = $wpdb->prefix . 'tbl_jobs';

    // # we set the default character set and collation for the table to avoid the "?" conversion.
    $charset_collate = $wpdb->get_charset_collate();

    if ($installed_version != $ar_db_version) { // use for upgrade table structure
        // # we add the SQL statement for creating a database table
        // The SQl statement must follow the rules of dbDelta function
        $sql = "CREATE TABLE $table_name (
          id mediumint(9) NOT NULL AUTO_INCREMENT,
          expire_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
          job_title tinytext NOT NULL,
          job_desc text NOT NULL,
          url varchar(150) DEFAULT '' NOT NULL,
          PRIMARY KEY  (id)
        ) $charset_collate;";

        // # To use the dbDelta class, we have to load this file, as it is not loaded by default
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

        // # call the dbDelta class
        dbDelta($sql);

        // # insert the new record if dbDelta can not find "ar_db_version" value. Or update the existing record if dbDeltab finds "ar_db_version" value.
        update_option("ar_db_version", $ar_db_version); 
    }
}

For the rewrite code above, we check the existing version to compare with the new version we set at $ar_db_version. Then we changed the URL length from 55 to 150. Finally, we replace add_option() with update_option() instead.

Calling the upgrade functions

After the plugin is activated, the register_activation_hook action hook won’t be called again. So to run the upgrade function above, we will use the plugins_loaded action hook instead. Add the following code to your main plugin file.

function mytable_update_db_check() {
    global $ar_db_version;
    if ( get_option( 'ar_db_version' ) != $ar_db_version ) {
        ar_customtable_install();
    }
}
add_action( 'plugins_loaded', 'mytable_update_db_check' );

Now, refresh the plugins page on the WordPress dashboard. Then go to check wp_tbl_jobs table structure and ar_db_version in wp_options table. You should see the changes we made (URL column length). Also, the version number is updated at ar_db_version in wp_options table.

The complete code

Here is the complete code in the main plugin file ( plugins/custom-table/custom-table.php ). I added the extra code for deleting the custom table and “ar_db_version” in the wp_options table when we deactivated the plugin as well. If you don’t want to delete those, you just leave this extra code out.

<?php

/**
 * Plugin Name:       Create a custom table in WordPress
 * Plugin URI:        https://example.com
 * Description:       Create a custom table in WordPress
 * Version:           1.0
 * Requires at least: 5.0
 * Requires PHP:      7.4.0
 * Author:            Apple Rinquest
 * Author URI:        https://applerinquest.com/
 * License:           GPL v2 or later
 * License URI:       https://www.gnu.org/licenses/gpl-2.0.html
 * Text Domain:       custom-table-plugin
 * Domain Path:       /languages
 */

if (!defined('ABSPATH')) {
    exit; // Exit if accessed directly.
}

// # calling the functions for create or update table structure and insert the initial data
register_activation_hook(__FILE__, 'ar_customtable_install');
register_activation_hook(__FILE__, 'ar_customtable_install_data');


// # database table versioning
global $ar_db_version;
// $ar_db_version = '1.0'; // initial version
$ar_db_version = '1.1';  // YOU MUST MANUALLY CHANGE THE VERSION NUMBER HERE FOR UPGRADE THE TABLE STRUCTURE.


/**
 * # create a database table
 */
function ar_customtable_install()
{
    global $wpdb;
    global $ar_db_version;
    $installed_version = get_option("ar_db_version"); // use for upgrade table structure

    // # we use the same table prefix in wp-config.php
    $table_name = $wpdb->prefix . 'tbl_jobs';

    // # we set the default character set and collation for the table to avoid the "?" conversion.
    $charset_collate = $wpdb->get_charset_collate();

    if ($installed_version != $ar_db_version) { // use for upgrade table structure
        // # we add the SQL statement for creating a database table
        // The SQl statement must follow the rules of dbDelta function. 
        // Read this post for the rules.
        $sql = "CREATE TABLE $table_name (
          id mediumint(9) NOT NULL AUTO_INCREMENT,
          expire_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
          job_title tinytext NOT NULL,
          job_desc text NOT NULL,
          url varchar(150) DEFAULT '' NOT NULL,
          PRIMARY KEY  (id)
        ) $charset_collate;";

        // # To use the dbDelta class, we have to load this file, as it is not loaded by default
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

        // # call the dbDelta class
        dbDelta($sql);

        // # insert the new record if dbDelta can not find "ar_db_version" value. Or update the existing record if dbDeltab finds "ar_db_version" value.
        update_option("ar_db_version", $ar_db_version);
    }
}



/**
 * # adding the initial data (optional)
 */
function ar_customtable_install_data()
{
    global $wpdb;

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


    // # our initial data
    $expire_date = strtotime('+15 days', date('Y-m-d'));
    $job_title = 'Backend Developer';
    $job_desc = 'developing and maintain the web applications and websites';
    $url = 'https://example.com/backend-dev-12345';

    // # insert our initial data into the database table
    $wpdb->insert(
        $table_name,
        array(
            'expire_date' => $expire_date,
            'job_title' => $job_title,
            'job_desc' => $job_desc,
            'url' => $url
        )
    );
}


/**
 * # upgrade the database table structure
 */
function customtbl_update_db_check()
{
    global $ar_db_version;

    // # if the $ar_db_version is changed then we will upgrade the table structure
    // Note that, you must manually change the version number of $ar_db_version (initial version is 1.0).
    // For example, the current version number is 1.0. We will change to 1.1 then refresh the WordPress dashboard.
    if (get_option('ar_db_version') != $ar_db_version) {
        ar_customtable_install();
    }
}
add_action('plugins_loaded', 'customtbl_update_db_check');



/**
 * # delete our custom table and remove the version number option from wp_options table 
 * after deactivate the plugin
 */
function ar_del_customtable()
{
    // delete our custom table
    global $wpdb;
    $table_name = $wpdb->prefix . "tbl_jobs";
    $sql = "DROP TABLE IF EXISTS $table_name;";
    $wpdb->query($sql);

    // delete our version number
    delete_option("ar_db_version");
}
register_deactivation_hook(__FILE__, 'ar_del_customtable');

So it is a long post here. You learn how to create the custom table, you learn how to upgrade the table structure and you learn how to handle the versioning. The custom table is useful if the original WordPress tables can not serve your requirements. One important thing, you need to remember, you must use the wpdb class properly (eg. $wpdb->prepare() $wpdb->query()) in order to avoid the SQL injection. Enjoy my post, please consider buying me a coffee 🙂

Wrap up

That’s it. I do come back and check my code from time to time and update it to make it work. The main point is you must understand each snippet. This way when you have any issue with the code, you can debug each snippet by yourself. Enjoy coding!