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