Creating custom tables WordPress Plugin

One of the essential knowledge required to develop plugins is to create additional tables in the WordPress database. Those custom tables allow developers to store specific information needed for their plugins rather than using the option table.

This tutorial shows you with simple steps how to create your custom table when the plugin is installed and how to delete the custom table when the plugin is deleted.

Step 1: Create the plugin directory and file
Create a directory called “bor-table” in the wp-content/plugin directory under the WordPress installation directory. In this directory, create a PHP file called “bor-table.php”

Step 2: Create the header file
Open the file “bor-table.php” and add this PHP comment block.

<?php
/*
Plugin Name: Create table
Plugin URI: http://blog.boroniatechnologies.com/plugin
Description: Plugin creating tables
Author: Lebasca
Version: 1.0
Author URI: http://blog.boroniatechnologies.com/
*/

Step 3: Activate the plugin
Below the header, add the following code to call the plugin activation function which is triggered when the plugin is activated in WordPress. The activation function calls the function “bor_install” to create the custom table.

register_activation_hook( __FILE__, 'bor_install' );

Step 4: Create the custom table
After the previous code, add the following code to create the custom table. This function uses WordPress’ wpdb class to create the table “wp_bor_software”.

function bor_install()
{
    /**Create an instance of the database class**/
    global $wpdb;

    /**Set the custom table name with the wp prefix "wp_bor_software"**/
    $table_name = $wpdb->prefix . "bor_software";

    /**Execute the sql statement to create or update the custom table**/
    $sql = "CREATE TABLE " . $table_name . " (
            id INT(9) NOT NULL AUTO_INCREMENT,
            software text NOT NULL,
            developer text NOT NULL,
            type text NOT NULL,
            license text NOT NULL,
       UNIQUE KEY id (id)
    );"
;

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

    /**Insert one record in the table by using the array method**/
    $ini_software = "Wordpress";
    $ini_developer = "Wordpress Foundation";
    $ini_type = "Blog";
    $ini_license = "GNU";

    $results = $wpdb->insert( $table_name, array( 'software' => $ini_software, 'developer' => $ini_developer, 'type' => $ini_type, 'license' => $ini_license ) );    

}

Step 5: Deactivate the plugin
Below the previous function, add the following code to call deactivation function which is triggered when the plugin is deactivated in WordPress. The activation function calls the function “bor_uninstall” to delete the custom table.

register_uninstall_hook( __FILE__, 'bor_uninstall' );

Step 6: Delete the custom table
After the previous code, add the following code to delete the custom table “wp_bor_software”>

function bor_uninstall(){

    /**Create an instance of the database class**/
    global $wpdb;

    /**Set the custom table name with the wp prefix "wp_bor_software"**/
    $db_table_name = $wpdb->prefix . "bor_software";

    /**Execute the sql statement to delete the custom table**/
    $sql = 'DROP TABLE IF EXISTS '.$db_table_name;
    $wpdb->query( $sql );
}

Step 7: Close the php syntax
Add the last code in your file “bor-table.php” to end the PHP syntax.

?>

Step 8: Test the creation of the custom table
Go to your WordPress Dashboard, click on the plugin link to see the plugin created “Create table” and click on Activate link to create the custom table. Then, go to your phpAdmin and check for the “wp_bor_software” table.

Step 9: Test the deletion of the custom table
Go to your WordPress Dashboard, click on the plugin link to see the plugin created “Create table”, click on Deactivate link and click on Delete link to delete the plugin files and the custom table. Before doing that, you might want to copy this plugin directory in another directory for future reference. Then, go to your phpAdmin and the “wp_bor_software” table is gone.

Summary: This tutorial gave you the basics on how to create custom tables with a plugin in WordPress. You learned the activate and deactivate functions and the wpdb class to manipulate the database.

About Me

Catherine Lebastard is a results-oriented professional with experience in web development, app development, and database design, analysis and administration. Bachelor degree in Computer Science and certified in Media Design and Web Development. Fluent in English and Spanish.


My skills

LANGUAGES: Visual.Net(C#,VB), Java, PHP, Python, HTML5, JavaScript, CSS3.
FRAMEWORKS: Django, Bootstrap, AngularJS
LIBRARIES: jQuery, Bootstrap
DATA VISUALIZATION: D3, NVD3, Tableau
CMS: Wordpress
ECOMMERCE: Shopify
DATABASES: Oracle, MS SQL Server, MySQL