preload
Nov 07

In this post I will explain how I use the WordPress database (MySQL) for the DiveBook plugin I am currently developing. For my plugin I am using the options table in WordPress for storing options and settings for my plugin. The DiveBook plugin will be storing information about dives and for this I am creating new tables in the WordPress database.

In my previous post “Getting started with WordPress plugin development” I talked about how to create a widget for the plugin and how to save options for the widget in the control panel. In the widget.php file I used the option table to store and retrieve the data.

The following code will store data to the options table when the user submits the options.

        //Store title and numdives in options table
        if ($_POST['divebook-submit']) {
            $options['title'] = strip_tags(stripslashes($_POST['divebook-title']));
            $options['numdives'] = strip_tags(stripslashes($_POST['divebook-numdives']));
            update_option('divebook', $options);
        }

The following code will retrieve the data stored above.

        // Get stored widget options
        $options = get_option('divebook');
        $title = $options['title'];
        $numdives = $options['numdives'];

Using the options table is very easy but it is not a good solution if you are going to store large amount of data and if you need a more complex database table design. When creating new tables it is a good idea to install a MySQL query tool so you can look at the database and tables you are creating. I installed the MySQL GUI Tools and I use the MySQL Query Browser to connect to and run queries on my WordPress database. In the image below you can see a query from the MySQL Query Browser and you can for example see the data stored for the DiveBook.

I want to create a table called dive that will for now just contain simple information about a dive. I create a new php file called database.php and I will  create and manage all the database tables needed by DiveBook in this file (the final version will have several tables linked with PK and FK). In the database.php file the table is created and I have also added table version so if you update your plugin you can force the database tables to be created even if they already exists. In the divebook.php (my main plugin file) I add an activation hook so that my divebook_db_create function will be called when the plugin is activated. I also include database.php in divebook.php.

divebook.php with activation hook and include.

<?php
//Include the database file
include("database.php");

//Activation hook so the DB is created when plugin is activated
register_activation_hook(__FILE__,'divebook_db_create');
?>

database.php with database table creation.

<?php
/**
 * Description: Creates database tables used by DiveBook
 * Author: Per Ola Saether
 */

//Database table versions
global $divebook_db_table_dive_version;
$divebook_db_table_dive_version = "1.0";

//Create database tables needed by the DiveBook widget
function divebook_db_create () {
    divebook_create_table_dive();
}

//Create dive table
function divebook_create_table_dive(){
    //Get the table name with the WP database prefix
    global $wpdb;
    $table_name = $wpdb->prefix . "dive";
    global $divebook_db_table_dive_version;
    $installed_ver = get_option( "divebook_db_table_dive_version" );
     //Check if the table already exists and if the table is up to date, if not create it
    if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name
            ||  $installed_ver != $divebook_db_table_dive_version ) {
        $sql = "CREATE TABLE " . $table_name . " (
              id mediumint(9) NOT NULL AUTO_INCREMENT,
              date bigint(11) DEFAULT '0' NOT NULL,
              site tinytext NOT NULL,
              description text NOT NULL,
              max_depth mediumint(9) NOT NULL,
              time mediumint(9) NOT NULL,
              UNIQUE KEY id (id)
            );";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
        update_option( "divebook_db_table_dive_version", $divebook_db_table_dive_version );
}
    //Add database table versions to options
    add_option("divebook_db_table_dive_version", $divebook_db_table_dive_version);
}
?>

When I now active my plugin I can see in MySQL Query Browser that the table dive is created with the correct columns.

Remember that if your plugin already is installed and activated you need to deactivate it and activate it so that the activation hook will call the create table function.

Follow me on twitter @PerOla

Share & enjoy
You can subscribe to my comments feed to keep track of new comments.

7 Comments to “Creating database tables for WordPress plugin”

  1. Your post adorn up my skills mate thanks for sharing.

  2. Rueta says:

    Hi you!

    I’m very like the way you example code for create database.

    I want make a widget which follow user post some data , store it into custom table database, and get date when i want show it.

    How can you demo some code write and get data from custom table database? For widget?
    Hope you reply me via Email , and this site :”> thankyou
    Trunglvt

  3. Oz says:

    I’ve been looking for a simple way to create a custom database in WP and you have shown me the way.
    Thanks for your efforts in sharing this information!

    Have a great day!

  4. Hi Rueta,

    I have not written a post on how to store/get data from custom table database, but it is quite straight forward and you will find it very well documented on the WordPress documentation page.

  5. Oz,

    I am glad you found this post helpful. Have a nice day :)

  6. Excellent post, realy i need it ,thanks for sharing

  7. Fahim says:

     ( 2012.02.23 17:53 ) : I precisely neeedd to thank you very much yet again. I do not know what I might have made to happen in the absence of the actual secrets shown by you on my area of interest. It actually was a difficult situation for me personally, but discovering the very specialised strategy you processed the issue took me to leap with fulfillment. I am happy for this advice and even hope you realize what an amazing job that you are accomplishing training some other people thru your site. Most probably you have never encountered all of us.

No Pingbacks to “Creating database tables for WordPress plugin”

Leave a Reply

Subscribe to my comments feed

Subscribe to my feeds Follow me on Twitter
DZone MVB