Add Custom Database Table to a WordPress Plugin

Adding a custom database table to a WordPress plugin is a simple 2 step process. If you are using the WordPress Plugin Boilerplate, adding a custom database table involves adding the following code to the activate function within the class-plugin-name-activator.php. This code includes utilizing the wpdb global variable and the dbDelta function that allows a custom SQL statement to be run on the database.

Step 1: Use dbDelta function to Add Custom Database Table to includes/class-plugin-name-activator.php file

The following code needs to be added to the activate method within the includes/class-plugin-name-activator.php file. You’ll notice in the code below that in addition to creating a custom database table, we also establish the plugin’s database version in an option that is saved to the user’s WordPress site. A plugin’s database version is recorded in case future adjustments are made to a database table, to allow you to accommodate those changes in future versions of your plugin.

global $wpdb;
$plugin_name_db_version = '1.0';
$table_name = $wpdb->prefix . "plugin_name_customers"; 
$charset_collate = $wpdb->get_charset_collate();

$sql = "CREATE TABLE $table_name (
		  id mediumint(9) NOT NULL AUTO_INCREMENT,
		  created timestamp NOT NULL default CURRENT_TIMESTAMP,
		  name tinytext NULL,
		  custom_field varchar(255) DEFAULT '' NOT NULL,
		  email varchar(255) DEFAULT '' NOT NULL,
		  UNIQUE KEY id (id)
		) $charset_collate;";

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

Step 2: Use dbDelta function to Delete Custom Database Table to includes/class-plugin-name-deactivator.php file

If you would like to remove the database when someone deactivates a plugin, they need to add corresponding code that deletes the database table. To do this, you need to add the following code to the deactivate method in the includes/class-plugin-name-deactivator.php file.

$table_name = $wpdb->prefix . "plugin_name_customers"; 

$sql = "DROP TABLE IF EXISTS $table_name";

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

Leave a Reply

Your email address will not be published. Required fields are marked *