create-custom-table-in-wp
Home » BLOG » WordPress » Create a custom table plugin in WordPress

Create a custom table plugin in WordPress

category:  WordPress

For WordPress, you can create the 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 a data from an external system into WordPress. 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. I don’t like to create the custom table in functions.php. Building the custom table via plugin allows to manage the table structure and versioning easier.

Here is my server info for this tutorial. In case, you wanna know.
PHP 7.4.0
MySQL 8.0.17
Apache 2.4.46

Create your own plugin

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

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 add the plugin name at the header in our main plugin file, so the plugin will appear in our Plugins page at 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 names “tbl_jobs“. The sample PHP code is below. The complete code is at the end. I want you to get the idea how to implement the plugin piece by piece.

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 the custom table that starts with the same your WordPress prefix, you can use $wpdb->prefix.

Creating our table

As you know that you can create the table using an SQL query directly in phpMyAdmin or other MySQL management tools (SQLyog). However, if you create the plugin for selling or share with other people, it is not convenience 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.

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)

Note that, all the rules refer to Creating Tables with Plugins. Below is an example of a 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

Sometime you may need to add the initial data in your custom table. Below is an example of how to do 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
    )
  );
}

Learn more on how to use the wpdb class, please follow this wpdb class link.

A complex SQL queries and SQL injection

https://codex.wordpress.org/Class_Reference/wpdb#Running_General_QueriesSometimes you may need to create the complex SQL queries. You can use the Data Validation.

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 table structure. Here is the sample of the code.

add_option( "ar_db_version", "1.0" );

Combine the figure A, B, C and D

Now we understand each piece of code 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.

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 ” as same as screenshot below.

custom table structure
wp_tbl_jobs table
tbl jobs initial data
initial data in wp_tbl_jobs table
tbl jobs options
ar_db_version in wp_options table

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

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 for activating 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 new version we set at $ar_db_version. Then we change 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 into 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' );

Note that, you must manually change the ar_db_version number from 1.0 to 1.1 (any number you want) in order to activate the upgrade function.

Now, refresh the plugins page at 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 make (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 add the extra code for deleting the custom table and ar_db_version in wp_options table when we deactivate the plugin as well. If you don’t want to delete those, you just comment 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.

Tips:
It would be a good idea if you add the function_exists function to all our functions. When you implement in your production, your function name may be used in other theme or plugins. To avoid the conflict with other providers, we should check our own functions with function_exists function.

However, the better way to avoid the conflict with other providers, is writing the code with OOP pattern. You can read “Building WordPress Plugins with OOP pattern” and write your code. In this tutorial, I don’t want the post looks too complex. So I don’t add extra stuff here.

That’s it. I do come back and check my code time to time and update 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. If you have any issues here, copy the error and go to stackoverflow.