WordPress provides plugin developers the option to store settings or data specific to a plugin into to the options database table (wp_options) made easy with a number of helper functions such as add_option, update_option and delete_option to add, update and delete data in the options table respectively.
Data stored in the options table should be a key/value pair.
According to a performance rule of thumb, any data type with a complicated structure should go into custom table.
Say there is a need to store data grouped into various column in form of a table-like structure; although such data can be stored in the options table; performance-wise, that would be inefficient. Such data are better off being stored in a separate DB table.
In today’s tutorial, I will show us how to create tables in WordPress database via a plugin on activation.
First, create a variable containing the table name to be created.
global $wpdb;
$table_name = $wpdb->prefix . 'sandbox';
Note: Table name must be prefixed with $wpdb->prefix for the following reasons:
* Without it, dbDelta() won’t create the database.
* Most folks using WordPress have their database table prefix changed from the default wp_ hence the $wpdb->prefix to programmatically get the table prefix.
Next is the SQL statement to create the table
[sql]
$sql = “CREATE TABLE $table_name (
id mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
title varchar(50) NOT NULL,
structure longtext NOT NULL,
author longtext NOT NULL,
PRIMARY KEY (id)
);”;
[/sql]
To use the function dbDelta() in executing SQL statements, the file containing the function must be required.
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
Pass the SQL statement as an argument to dbDelta().
dbDelta( $sql );
To create the table when the plugin is activated, include the codes above in a function or class method and hook the function to activate_PLUGINNAME Action via the register_activation_hook wrapper function.
Full code below:
function create_plugin_database_table() {
global $wpdb;
$table_name = $wpdb->prefix . 'sandbox';
$sql = "CREATE TABLE $table_name (
id mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
title varchar(50) NOT NULL,
structure longtext NOT NULL,
author longtext NOT NULL,
PRIMARY KEY (id)
);";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
}
register_activation_hook( __FILE__, 'create_plugin_database_table' );
If you intend to include the create_plugin_database_table() function in a class, make the function/method static and then register it to the activation hook as follows:
class Class_Name {
public static function create_plugin_database_table() {
//.. insert the code
}
}
register_activation_hook( __FILE__, array( 'Class_Name', 'create_plugin_database_table' ) );
More information on creating tables with plugins available at WordPress codex