WordPress:Creating Tables with Plugins

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:

• Setup information -- user choices that are entered when the user first sets up your plugin, and don't tend to grow much beyond that (for example, in a tag-related plugin, the user's choices regarding the format of the tag cloud in the sidebar). Setup information will generally be stored using the WordPress options mechanism.
• Data -- information that is added as the user continues to use your plugin, which is generally expanded information related to posts, categories, uploads, and other WordPress components (for example, in a tag-related plugin, the tag names and correspondence between tags and articles). Data will generally be stored in a separate MySQL table, which will have to be created.

This article describes how to have your plugin automatically create a MySQL 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 WordPress:phpMyAdmin. But neither of those options is very satisfactory, since a user could easily forget to run the install script or screw 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:

1. Write a PHP function that creates the table.
2. Ensure that WordPress calls the function when the plugin is activated.
3. Create an upgrade function, if a new version of your plugin needs to have a different table structure.

Create 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 database. For purposes of this article, we'll assume you want to call this function jal_install.

Database Table Prefix

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

Is the Table Already There?

The next step in creating the table is to see if the table has already been created. The following if statement runs a SHOW TABLES SQL query to try to find the table, and then compares the result to our table name:

    if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {

Creating or Updating the Table

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/upgrade-functions.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:

• You have to put each field on its own line in your SQL statement.
• You have to have two spaces between the words PRIMARY KEY and the definition of your primary key.
• You must use the key word KEY rather than its synonym INDEX

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:

$sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

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

Adding Initial Data

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!";

  $insert = "INSERT INTO " . $table_name .
            " (time, name, text) " .
            "VALUES ('" . time() . "','" . $wpdb->escape($welcome_name) . "','" . $wpdb->escape($welcome_text) . "')";

  $results = $wpdb->query( $insert );

NOTE: Even though we defined $welcome_name and $welcome_text in this function and know that there are no SQL special characters in them, it's still a good idea to always run a variable through the $wpdb->escape function before passing it to the database to prevent security problems and random bugs.

A Version Option

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

The Whole Function

This function is done. Let's see it all in one piece. Note that the version number is now stored in a global variable.

$jal_db_version = "1.0";

function jal_install () {
   global $wpdb;
   global $jal_db_version;

   $table_name = $wpdb->prefix . "liveshoutbox";
   if($wpdb->get_var("show tables like '$table_name'") != $table_name) {
      
      $sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

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

      $welcome_name = "Mr. Wordpress";
      $welcome_text = "Congratulations, you just completed the installation!";

      $insert = "INSERT INTO " . $table_name .
            " (time, name, text) " .
            "VALUES ('" . time() . "','" . $wpdb->escape($welcome_name) . "','" . $wpdb->escape($welcome_text) . "')";

      $results = $wpdb->query( $insert );
 
      add_option("jal_db_version", $jal_db_version);

   }
}

Calling the function

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

See WordPress:Function_Reference/register_activation_hook for more details.

Adding an Upgrade Function

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.

Note that you will need to make sure that the function gets called. So tell your plugin users that when they upgrade, they need to deactivate the plugin, copy in the new plugin file, and then activate the plugin again.

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:

   $installed_ver = get_option( "jal_db_version" );

   if( $installed_ver != $jal_db_version ) {

      $sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(100) NOT NULL,
	  UNIQUE KEY id (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.

Resources

For further reading on plugin development, check out WordPress: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