Languages: English • Tables with Plugins 日本語 Русский • (Add your language)
If you are writing a plugin for WordPress, you will almost certainly find that you need to store some information in the WordPress database. There are two types of information you could store:
This article describes how to have your plugin automatically create a MySQL/MariaDB table to store its data. Note that as an alternative to following the steps here, you could have the plugin user run an install script when they install your plugin. Another approach would be to have the user execute an SQL query on their own, using something like phpMyAdmin. But neither of those options is very satisfactory, since a user could easily forget to run the install script or mess up the query (and they might not have phpMyAdmin available).
So, it is recommended that you follow the steps below to have your plugin automatically create its database tables:
The first step in making your plugin create database tables automatically is to create a PHP function within your plugin that adds a table or tables to the WordPress MySQL/MariaDB database. For purposes of this article, we'll assume you want to call this function jal_install.
In the wp-config.php file, a WordPress site owner can define a database table prefix. By default, the prefix is "wp_", but you'll need to check on the actual value and use it to define your database table name. This value is found in the $wpdb->prefix variable. (If you're developing for a version of WordPress older than 2.0, you'll need to use the $table_prefix global variable, which is deprecated in version 2.1).
So, if you want to create a table called (prefix)liveshoutbox, the first few lines of your table-creation function will be:
function jal_install () {
global $wpdb;
$table_name = $wpdb->prefix . "liveshoutbox";
}
The next step is to actually create the database table. Rather than executing an SQL query directly, we'll use the dbDelta function in wp-admin/includes/upgrade.php (we'll have to load this file, as it is not loaded by default). The dbDelta function examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary, so it can be very handy for updates (see wp-admin/upgrade-schema.php for more examples of how to use dbDelta). Note that the dbDelta function is rather picky, however. For instance:
With those caveats, here are the next lines in our function, which will actually create or update the table. You'll need to substitute your own table structure in the $sql variable:
global $wpdb; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE $table_name ( id mediumint(9) NOT NULL AUTO_INCREMENT, time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL, name tinytext NOT NULL, text text NOT NULL, url varchar(55) DEFAULT '' NOT NULL, PRIMARY KEY (id) ) $charset_collate;"; require_once( ABSPATH . 'wp-admin/includes/upgrade.php' ); dbDelta( $sql );
Note: Above we set the default character set and collation for the table. If we don't do this, some characters could end up being converted to just ?'s when saved in our table. In this example we use $wpdb->get_charset_collate() to get the character set and collation. That function was introduced in WordPress 3.5, and if you need to support versions before that you will need create the charset/collate string yourself (you could copy the source of that function).
Finally, you may want to add some data to the table you just created. Here is an example of how to do that:
$welcome_name = 'Mr. WordPress'; $welcome_text = 'Congratulations, you just completed the installation!'; $table_name = $wpdb->prefix . 'liveshoutbox'; $wpdb->insert( $table_name, array( 'time' => current_time( 'mysql' ), 'name' => $welcome_name, 'text' => $welcome_text, ) );
NOTE: For more on using WPDB, see wpdb class. In this case, we're using $wpdb->insert, so our data will automatically be escaped. If you need to use another method like $wpdb->query instead, it's a good idea to run the variables through the $wpdb->prepare function before passing the query to the database to prevent security problems, even though we defined $welcome_name and $welcome_text in this function and know that there are no SQL special characters in them.
Another excellent idea is to add an option to record a version number for your database table structure, so you can use that information later if you need to update the table:
add_option( "jal_db_version", "1.0" );
This function is done. Let's see it all in one piece. Note that the version number is now stored in a global variable.
<?php
global $jal_db_version;
$jal_db_version = '1.0';
function jal_install() {
global $wpdb;
global $jal_db_version;
$table_name = $wpdb->prefix . 'liveshoutbox';
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE $table_name (
id mediumint(9) NOT NULL AUTO_INCREMENT,
time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
name tinytext NOT NULL,
text text NOT NULL,
url varchar(55) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
) $charset_collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
add_option( 'jal_db_version', $jal_db_version );
}
function jal_install_data() {
global $wpdb;
$welcome_name = 'Mr. WordPress';
$welcome_text = 'Congratulations, you just completed the installation!';
$table_name = $wpdb->prefix . 'liveshoutbox';
$wpdb->insert(
$table_name,
array(
'time' => current_time( 'mysql' ),
'name' => $welcome_name,
'text' => $welcome_text,
)
);
}
Now that we have the initialization function defined, we want to make sure that WordPress calls this function when the plugin is activated by a WordPress administrator. To do that, we will use the activate_ action hook. If your plugin file is wp-content/plugins/plugindir/pluginfile.php, you'll add the following line to the main body of your plugin:
register_activation_hook( __FILE__, 'jal_install' ); register_activation_hook( __FILE__, 'jal_install_data' );
See Function_Reference/register_activation_hook for more details.
Over the lifetime of your plugin, you may find that you need to change the plugin's database structure in an upgraded version. To do that, you will need to create update code within your plugin that will detect that a new version has been installed, and upgrade the database structure. The easiest thing to do is to add the code to the jal_install function we just created.
So, let's assume that the function above was used to create database version 1.0 of your plugin, and you are now upgrading to version 1.1 so that the URL field can be wider (100 characters instead of 55). You will need to add the following lines to the end of your jal_install function, to check the version and upgrade if necessary:
<?php
global $wpdb;
$installed_ver = get_option( "jal_db_version" );
if ( $installed_ver != $jal_db_version ) {
$table_name = $wpdb->prefix . 'liveshoutbox';
$sql = "CREATE TABLE $table_name (
id mediumint(9) NOT NULL AUTO_INCREMENT,
time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
name tinytext NOT NULL,
text text NOT NULL,
url varchar(100) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
);";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
update_option( "jal_db_version", $jal_db_version );
}
You'll also need to change the global $jal_db_version variable at the top of the file, and of course you'll want to change the initialization section created above to use the new table structure.
Since 3.1 the activation function registered with register_activation_hook() is not called when a plugin is updated. So to run the above code after the plugin is upgraded, you need to check the plugin db version on another hook, and call the function manually if the the database version is old. Like this:
function myplugin_update_db_check() {
global $jal_db_version;
if ( get_site_option( 'jal_db_version' ) != $jal_db_version ) {
jal_install();
}
}
add_action( 'plugins_loaded', 'myplugin_update_db_check' );
For further reading on plugin development, check out Plugin Resources, a comprehensive list of plugin resources. You may also find this post from the wp-hackers mailing list to be helpful: WordPress Hackers Mailing List: Answer to Plugin Requires Additional Tables. Also see: Post meta vs separate database tables.