» » » Create a custom table plugin in WordPress

Create a custom table plugin in WordPress

posted in: WordPress

For WordPress, most of the time you can create the custom post type, custom taxonomy or custom category that fits your need. But sometimes you may need your own table structure to store your data. For example, you sync the data from the external system into WordPress. Today I gonna share with you how to create a simple custom table via the plugin.

In this tutorial, we will do three steps below

Create a database table

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

function jal_install() {
    global $wpdb;

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

    // adding SQL statement
}
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 the 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 convenient for them.

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 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 here. Below is an example of a SQL statement regarding the rules.

function jal_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 "?" convertion.
  // 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 jal_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 link.

A complex SQL queries and SQL injection

Sometimes you may need to create the complex SQL queries. You can use the here.

A version option

The version option is very important for updating the database table structure in WordPress. We use this option to record a version number for our database table structure. Here is the sample of the code.

add_option( "jal_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 here.

<?php
// # our databse table version
global $jal_db_version;
$jal_db_version = '1.0';


/**
 * create a database table
 */
function jal_install()
{
  global $wpdb;
  global $jal_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 "?" convertion.
  $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('jal_db_version', $jal_db_version);
}



/**
 * adding the initial data (optional)
 */
function jal_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 at the WordPress admin panel. To do that, we will use the register_activation_hook action hook.

At the main file of your plugin, add the code below.

register_activation_hook( __FILE__, 'jal_install' );
register_activation_hook( __FILE__, 'jal_install_data' );

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

wp_tbl_jobs table
jal_db_version option_name

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 jal_install function and use the jal_db_version option for activating the upgrade.

Let’s say, we want to extend the URL field length from 55 to 150. Here is an example code for upgrading.

<?php

global $wpdb;
$installed_version= get_option( "jal_db_version" );

if ( $installed_version!= $jal_db_version ) {

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

	$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)
	);";

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

	update_option( "jal_db_version", $jal_db_version );
}

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. The PHP code is below.

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

Note that, you must manually change the jal_db_version number from 1.0 to 1.1 (any number you want) in order to activate the upgrade. Please see the complete code below.

The complete code

Here is the complete code in the main plugin file. My plugin folder names “sample-custom-table“. My main plugin file names “sample-custom-table.php“. So the code below is in “sample-custom-table.php“.

<?php

/**
 * Plugin Name: Create a custom table in WordPress
 * Plugin URI: https://applerinquest.com
 * Description: Create a custom table in WordPress
 * Version: 1.0
 * Author: Apple Rinquest
 * Author URI: https://applerinquest.com
 */

//  # calling the functions
register_activation_hook(__FILE__, 'jal_install');
register_activation_hook(__FILE__, 'jal_install_data');


// # our databse table version
global $jal_db_version;

// # for upgrading the database table structure with code below, you must manully change the version number here.
// $jal_db_version = '1.0';  // first version
$jal_db_version = '1.1';  // change the version in order to upgrade the table structure


/**
 * create a database table
 */
function jal_install()
{
  global $wpdb;
  global $jal_db_version;

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

  // # get the current jal_db_version version number from wp_options table
  $installed_version = get_option('jal_db_version');

  // # check if no version number is found, we will create the new table
  if (strlen($installed_version) == 0) {

    // # we set the default character set and collation for the table to avoid the "?" convertion.
    $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('jal_db_version', $jal_db_version);

    //
  } else if ($installed_version != $jal_db_version) {  // upgrade the database table structure if the version number is changed.

    // # we set the default character set and collation for the table to avoid the "?" convertion.
    $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(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);


    // # update our database table version
    update_option('jal_db_version', $jal_db_version);
  }
}



/**
 * adding the initial data (optional)
 */
function jal_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 mytable_update_db_check()
{
  global $jal_db_version;

  // # if the $jal_db_version is changed then we will upgrade the table structure
  // Note that, you must manually change the version number of $jal_db_version.
  // For example, the current version number is 1.0. We will change to 1.1 then refresh the WordPress admin panel.Then the code below will run.
  if (get_option('jal_db_version') != $jal_db_version) {
    jal_install();
  }
}
add_action('plugins_loaded', 'mytable_update_db_check');

That’s it. You can add the code for doing something when your plugin is deactivated using the register_deactivation_hook action hook. For example, delete your custom table and the version number in the wp_options table after your plugin is deactivated.