WordPress Plugin Development – Part 1 Tables

Welcome to part one of my crash course on WordPress plugin development, how to work with tables. A properly formed WordPress plugin will often not only store information in one or more database tables, but it will also do something specific with that data on deactivation of the plugin.

Prerequisites:

Since this is the first lesson in my Crash Course on WordPress Plugin Development, the only additional reading in this course would be the primer of the series where I demonstrate how to initiate a plugin. In the primer, I explain a plugin’s file structure and how to start your first plugin file in PHP. Click the following link to read the primer for this tutorial:

Crash Course on WordPress Plugin Development

Other requirements are the same for the entire series of WordPress plugin tutorials and include:

  • Knowledge of PHP
  • Experience in HTML and HTML5
  • Knowledge of JavaScript
  • CSS experience
  • WordPress experience
  • Knowledge of MySQL database

Planning your plugin data needs

The first thing to do when planning out your plugins database is to plan out your data needs for both activation and deactivation. On activation, a plugin will usually create one or more tables to store data for the plugin such as plugin settings and user data. Then some plugins will also store some data in one or more of the tables it has created such as default settings or test content.

On deactivating a plugin you need to consider the best ways to handle the data. For example, a lot of the time, plugin developers opt to leave tables in place in case the user reactivates the plugin. That is usually a good idea since developers will often deactivate and reactivate plugins while testing and debugging a WordPress site. Imagine a plugin that stores user information such as a guest book plugin. The user has stored many entries in their guest book that is dependent on a table created by a plugin. If the plugin is deactivated and reactivated and the plugin is programmed to delete the table, the user will loose all their entries and be quite upset no doubt. So as you can see, it is important to consider how a plugin will be handled in real-life when considering your deactivation functions. However,  there are some cases where a plugin developer may wish to delete some tables when the plugin is deactivated. If a tables doesn’t contain irreplaceable data, then it may be best to delete it, especially if it can very easily be replace when reactivating the plugin. Another option is to give the plugin user the option to either delete or save the database tables on deactivation. In this tutorial we will cover both how to create and delete tables within a plugin using the proper WordPress hooks.

Enough gibber jabber, Here is the PHP code to make a complete working example of a simple plugin that creates a table on activation, adds a row of data and then deletes the table on deactivation of the plugin. Remember, if you want to leave the table on deactivation so when a user reactivates the plugin, the retain their data, skip the code to delete the table. I included it here as an option, not a requirement for your plugin.

<?php
/**
 * Plugin Name: Jafty Plugin
 * Plugin URI: http://jafty.com/blog/crash-course-on-wordpress-plugin-development/
 * Description: A brief description of how to create a Plugin.
 * Version: 1.0
 * Author: Ian L. of Jafty.com
 * Author URI: http://jafty.com
 * License: GPL2
 */
 
 
 
function install_tbl () {
global $wpdb;
    $table_name = $wpdb->prefix . ‘jafty_plugin’;

$sql = “CREATE TABLE $table_name (
  id int(9) NOT NULL AUTO_INCREMENT,
  time datetime DEFAULT ‘0000-00-00 00:00:00’ NOT NULL,
  field varchar(25) DEFAULT NULL,
  content varchar(999) DEFAULT NULL,
  UNIQUE KEY id (id)
)”;

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

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

//Now add some data to the table:
$field_name = ‘item’;
$field_content = ‘this is the item description’;

$wpdb->insert(
    $table_name,
    array(
        ‘time’ => current_time(‘mysql’),
        ‘field’ => $field_name,
        ‘content’ => $field_content,
    )
);

}//end install_tbl function

//call install_tbl function when plugin is activated by admin:
register_activation_hook( __FILE__, ‘install_tbl’ );

function pluginUninstall() {

        global $wpdb;
        $table = $wpdb->prefix.”jafty_plugin”;

        //Delete any options thats stored also?
    //delete_option(‘wp_yourplugin_version’);

    $wpdb->query(“DROP TABLE IF EXISTS $table”);
}//end pluginUninstall function

//hook into WordPress when its being deactivated:
register_deactivation_hook( __FILE__, ‘pluginUninstall’ );
?>

Code Explained

The above code is fairly simple. It consists of two functions followed by two hooks to hook into WordPress at activation and deactivation time. You will want to make some adjustments to the code to meet your own plugin requirements. The sql variable can be edited by changing the field names of id, time, field and content to the field names you need and edit the rest of the sql statement accordingly. If you have any experience with MySQL, this will come easy for you. If you don’t, you can find the official MySQL documentation page at http://dev.mysql.com/doc/

One Reply to “WordPress Plugin Development – Part 1 Tables”

Leave a Reply

Your email address will not be published. Required fields are marked *