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