How to create a database table when activating a WordPress theme

Recently,we came across the need of automatically creating a database table, the first time a WordPress theme is activated.

Over at Lab21 we create our own themes for each project. This gives us the power of doing whatever we want, with no limitations.

So for this, we have created our own WordPress “naked” theme, pre-stocked with all the functionalities we always use. Having pre-sets in our theme saves us a lot of time: PHP functions that we have created and use,  the basic structure of our JS and SASS (CSS) files.

This way we have created a few functionalities, for which we prefer to write in a specific database table, in order to keep things clean in the default tables of WordPress. Use case: it is very common to have a few scattered words or titles in our pages that our clients would want to be able to edit through the admin panel of WordPress.
For example, a “read more” link or a “Latest news” title in the home page.
For this, we have created our own mechanism for these “Translations”, for which we need to create a new database table every time we start on the project.

As it is easy to forget to create a new table every time you start on a project, we needed a way to create this table automatically through WordPress, the first time we would activate our theme.

Luckily, WordPress offers such a feature, by using the after_switch_theme action and the dbDelta function.

HOW TO CREATE A DATABASE TABLE AFTER ACTIVATING YOUR WORDPRESS THEME

add_action("after_switch_theme", "mytheme_create_extra_table");

function mytheme_create_extra_table(){
    global $wpdb;

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

    $table_name = $wpdb->prefix . "translations";  //get the database table prefix to create my new table

    $sql = "CREATE TABLE $table_name (
      id int(10) unsigned NOT NULL AUTO_INCREMENT,
      identifier varchar(255) NOT NULL,
      translation varchar(255) NOT NULL,
      lang varchar(5) NOT NULL,
      notes varchar(255) DEFAULT NULL,
      PRIMARY KEY  (id),
      KEY Index_2 (lang),
      KEY Index_3 (identifier)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;";

    dbDelta( $sql );
}

The “create table” command above is the same as in MySQL, so you could create the table in PhpMyAdmin the first time and copy from there the create statement.

Creating a table in PhpMyAdmin

The table is only created once through the dbDelta function, right after our theme is activated in WordPress Admin: Appearance -> Themes.

However, there are some notes that you should keep in mind:

  • You must put each field on its own line in your SQL statement.
  • You must 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 and you must include at least one KEY.
  • KEY must be followed by a SINGLE SPACE then the key name then a space then open parenthesis with the field name then a closed parenthesis.
  • You must not use any apostrophes or backticks around field names.
  • Field types must be all lowercase.
  • SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.
  • You must specify the length of all fields that accept a length parameter. int(11), for example.

For more information you can visit the WordPress Codex.