Creating Database Tables During Install

 

Your plugin is activated each time a user accesses a page. WP does not give you a simple way to track if that activation means your plugin is installed for the first time, being upgraded, or just another page access. The template code from this site differentiates these component life cycle events and gives you a place to write code for them. One such important event is installation. By installation, this actually means the first activation after install. Read about the general mechanism of add Install/Uninstall Actions. That article describes different places to put code to be executed during installation. (Also see where to put code to execute during an update in Update Actions.

The most common thing to do during installation is to create a database table that your plugin will use. There is a specific function already for this purpose. All you need to do is override it in you XXX_Plugin class.

Locate this function which should be already in your XXX_Plugin class:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/**
 * See: http://plugin.michael-simpson.com/?page_id=101
 * Called by install() to create any database tables if needed.
 * Best Practice:
 * (1) Prefix all table names with $wpdb->prefix
 * (2) make table names lower case only
 * @return void
 */
protected function installDatabaseTables() {
    //        global $wpdb;
    //        $tableName = $this->prefixTableName('mytable');
    //        $wpdb->query("CREATE TABLE IF NOT EXISTS `$tableName` (
    //            `id` INTEGER NOT NULL");
}

To create a table, simply uncomment these three lines and change ‘mytable’ to the table name you wish to use.

Note: the “prefixTableName” function actually adds two prefixes to the table name. The function is:

1
2
3
4
5
6
7
8
9
10
11
/**
 * @param  $name string name of a database table
 * @return string input prefixed with the WordPress DB table prefix
 * plus the prefix for this plugin (lower-cased) to avoid table name collisions.
 * The plugin prefix is lower-cases as a best practice that all DB table names are lower case to
 * avoid issues on some platforms
 */
protected function prefixTableName($name) {
    global $wpdb;
    return $wpdb->prefix .  strtolower($this->prefix($name));
}

The $wpdb->prefix is a WP convention to prefix all tables with “wp_”. This prefix can be changed on a WP installation. This allows for more than one WP installation to share the same DB by simply using different table prefixes (and hence different tables).

This template code uses the convention that things like table names and options should be prefixed with the name of the plugin (actually the “XXX” in your “XXX_Plugin”) to avoid having the same names as other plugins. The use of $this->prefixTableName(), $this->prefix() and option handling functions like $this->getOption() are convenience functions to make this prefixing somewhat transparent.

If you use $this->prefixTableName(‘mytable’) in your MyAwesomePlugin_Plugin.php file, you would end up a tabled named “wp_myawesomeplugin_mytable”. Unlike option prefixes, table name prefixes are lower-cased because mixed case table names can cause a problem when you transfer a MySQL DB content between MySQL instances on Unix and Windows.

Dropping DB Tables on Uninstall

A good plugin citizen should clean up after itself. If your plugin creates DB tables on install, it makes sense to drop them on uninstall. To do this, override your unInstallDatabaseTable() function in your XXX_Plugin class and drop your tables there.

But it is not always that simple. Sometimes when a user has a problem with a plugin, he will try uninstalling it and installing it again. He may not intend for all his data to be removed.

One way I have dealt with that in the past is to create an option for the plugin where the plugin administration can choose if the tables should be dropped on uninstall. For example, I created a “DropOnUninstall” option like this (See Handling Options)

1
2
3
4
5
6
7
public function getOptionMetaData() {
    //  http://plugin.michael-simpson.com/?page_id=31
    return array(
        //'_version' => array('Installed Version'), // Leave this one commented-out. Uncomment to test upgrades.
        //'DropOnUninstall' => array(__('Drop this plugin\'s Database table on uninstall', 'TEXT_DOMAIN'), 'false', 'true')
    );
}

Then I added a check in the unInstallDatabaseTable() function like what is shown commented-out in the template code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/**
 * See: http://plugin.michael-simpson.com/?page_id=101
 * Drop plugin-created tables on uninstall.
 * Issue: sometimes people have issues with a version of the plugin and they will
 * uninstall with the intention of immediately re-installing. This will cause loss of
 * data. Good practice is to set an option for whether or not to drop tables on uninstall
 * @return void
 */
protected function unInstallDatabaseTables() {
    //        // if you use this 'DropOnUninstall' be sure to add it to the array in getOptionMetaData()
    //          and you should also guard deleteSavedOptions()
    //        if ('true' === $this->getOption('DropOnUninstall', 'false')) {
    //            global $wpdb;
    //            $tableName = $this->prefixTableName('mytable');
    //            $wpdb->query("DROP TABLE IF EXISTS `$tableName`");
    //        }
}

 

  19 Responses to “Creating Database Tables During Install”

  1. Hello,

    I try to add a table but there is no table in the database.
    Please can you tell me what i do wrong.

    Jan

  2. i can create my table while activating plugin but while i deactivate my plugin itself it is getting drop from database. how to drop the table while uninstalling.

  3. The table is not create in the database :((

  4. In the XXX_Plugin.php, the installDatabaseTables are declared as “protected.” Changing it to “public” calls the function. The same is true for the unInstallDatabaseTables.

    Happy programming.

    • That did not seem to fix it. As per my post below, there seems to be 2 functions in 2 separate files that do the same thing and neither seem to create a database.

  5. That did not seem to fix anything. There are two identical functions for each install db and uninstall db. One is in XXX_plugin.php and the other in XXX_LifeCycle.php

    Changing either does not seem to get my database made. Its very weird. So far everything has been amazing with this template.. just stuck because everything looks like it should work fine, but I can not get any feed back from the install procedure.

    • XXX_LifeCycle.php defines empty functions and calls them. XXX_Plugin.php is a subclass (Object Oriented) so it inherits those functions. The intention is that you would not change XXX_LifeCycle but would redefine (override) the functions in XXX_Plugin so those get called instead. Perhaps you broke something in XXX_LifeCycle? The XXX_init.php gets run by WordPress, which then runs XXX_Plugin’s install(), activate(), deactivate() as appropriate. XXX_Plugin’s install() is really in XXX_LifeCycle (it inherits it). That function calls others like installDatabaseTables() which you can redefine in XXX_Plugin.

      You might try setting up a logging message in installDatabaseTables() to see if it gets called, and perhaps print out the results of your database call in case there is an error.

    • Same here! I also declared them public, to no avail.

  6. What I found, concerning this issue.
    1. Uninstall functionality hasn’t built in template – I see no ‘register_uninstall_hook’ in files.
    2. I was confusing terms Deactivate and Uninstall. Deactivation doesn’t cause tables cleanup.
    3. First activation of a plugin precedes by installation – that’s the time when install callbacks run.
    3. When you write normal mysql dump in installDatabaseTables()
    $wpdb->query(“DROP TABLE IF EXISTS `wp_antiddos`;
    CREATE TABLE IF NOT EXISTS `wp_tablename`…”);
    CREATE TABLE do not executed. Write 2 query() calls instead.
    4. When you have NOT created tables (see before) none of Activation do NOT involve install callbacks.
    5. You can run Installation again – you should manually
    select * from wp_options where option_name rlike ‘_installed’
    find there your plugin’s option and delete it.

  7. Can give an example of a plugin that makes the student database tables and bring the tables in the admin page.

  8. he table is not create in the database

  9. Hi,
    Where, and how, do I create a Custom post type on install/activation. I have tried to put the code on various places but they never gets created.

    This is a great guide by the way!
    Thanks!

  10. this worked for me…….
    $wpdb->query( “CREATE TABLE IF NOT EXISTS `$tableName`
    (`id` INTEGER NOT NULL )”);

  11. Thanks for the info!

    I am following this course https://www.youtube.com/watch?v=SnB5lkcRRhA, but the steps of the manual installation are different. Any advice?

    Thank you

  12. I add it to Plugin.php: register_activation_hook(__FILE__, function()
    {
    global $wpdb;

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

    dbDelta(“CREATE TABLE IF NOT EXISTS `{$wpdb -> prefix}my_table` (
    `id` INT(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL,
    `date_create` INT(10) UNSIGNED NOT NULL
    ) {$wpdb -> get_charset_collate()};”);
    });

Leave a Reply to Jan Cancel reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>