Create a Plugin With Its Own Custom Database Table

In 99% of the currently available WordPress plugins, there is absolutely no need for them to have a custom table – WordPress utilizes the ‘options’ table to help in most cases.  However, what if your plugin requires it’s own “space” in the WordPress database?

It really is a relatively easy thing to do – I’ll show you how to create a new table using a built-in WP function and then how to verify the table exists and write to it.  This tutorial uses code snippets from an upcoming plugin which will be exclusive to WP Toy (and launching soon) – so ensure you sign up to the RSS feed to keep up-to-date!

Here’s a quick glimpse at the final code if you want to get going straight away:

{code type=php}

#To allow this to be as extensible as possible, make sure $table_prefix is globalised, we also need the $wpdb class functions too
global $table_prefix, $wpdb;

#Create the 'name' of our table which is prefixed by the standard WP table prefix (which you specified when you installed WP)
$wp_track_members_table = $table_prefix . "wp_track_members";

#Check to see if the table exists already, if not, then create it
if($wpdb->get_var("show tables like '$wp_track_members_table'") != $wp_track_members_table) {

$sql0 = "CREATE TABLE `". $wp_track_members_table . "` ( ";
$sql0 .= " `page_load_id` int(11) NOT NULL auto_increment, ";
$sql0 .= " `todays_date` date NOT NULL default '0000-00-00', ";
$sql0 .= " `todays_time` time NOT NULL default '00:00:00', ";
$sql0 .= " UNIQUE KEY `page_load_id` (`page_load_id`) ";
$sql0 .= ") ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; ";

#We need to include this file so we have access to the dbDelta function below (which is used to create the table)
require_once(ABSPATH . 'wp-admin/upgrade-functions.php');
dbDelta($sql0);
}

}
?>
# The Hook - you can choose which hook you want to use, for this instance, we'll run this script every time the footer is loaded
add_action('wp_footer', 'createtable_wp_track_members');
?>
{/code}

Stage 1: Declare the function and the global variables we need

{code type=php}
global $table_prefix, $wpdb;

...
}
?>
{/code}

Stage 2: Give our table a name and use the WP prefix

{code type=php}
….
$wp_track_members_table = $table_prefix . "wp_track_members";
....
}
?>
{/code}

Stage 3: Check that the table doesn’t already exist and if not use MySQL statements to create the table

{code type=php}
....
if($wpdb->get_var("show tables like '$wp_track_members_table'") != $wp_track_members_table) {

$sql0 = "CREATE TABLE `". $wp_track_members_table . "` ( ";
$sql0 .= " `page_load_id` int(11) NOT NULL auto_increment, ";
$sql0 .= " `todays_date` date NOT NULL default '0000-00-00', ";
$sql0 .= " `todays_time` time NOT NULL default '00:00:00', ";
$sql0 .= " `member_id` varchar(255) NOT NULL default '', ";
$sql0 .= " `ip` varchar(255) NOT NULL default '', ";
$sql0 .= " `requrl` varchar(255) NOT NULL default '', ";
$sql0 .= " `page_id` varchar(255) NOT NULL default '', ";
$sql0 .= " `first_visited_date` date NOT NULL default '0000-00-00', ";
$sql0 .= " `first_visited_time` time NOT NULL default '00:00:00', ";
$sql0 .= " `number_of_visits` int(11) NOT NULL default '1', ";
$sql0 .= " UNIQUE KEY `page_load_id` (`page_load_id`) ";
$sql0 .= ") ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; ";

}#End of If() function to see if table exists
?>
{/code}

This chunk of code looks a little daunting at first but it's simply a SQL statement spread across several lines for clarity. We're creating a table with the name of the variable we set earlier ($wp_track_members_table) then declaring the table fields, in this case page_load_id, todays_date and todays_time – they’re fairly self explainatory. Then we make the page_load_id a unique key and give the default engine.

Now we have our sql statement written, we need a function which will actually run it. Luckilly, WP has one built in:

Stage 4: Running the sql statement

{code type=php}
….
require_once(ABSPATH . 'wp-admin/upgrade-functions.php');
dbDelta($sql0);
....
?>
{/code}

Finally, we need a way to tell WordPress when we want to run this function. As this should only happen once – i.e. the table is only created once, then it doesn’t really matter where this is put. However, for this example, we’ll be running this script when the footer is loaded. To do this, WP has a hook called wp_footer …

Stage 5: Hooking this into WordPress

{code type=php}
# The Hook
add_action('wp_footer', 'createtable_wp_track_members');
?>
{/code}

The next time the Footer is loaded, this table will be created in the WP database. You can check it's existence using any number of resource. My personal favorite is sqlBuddy.

So now we have a table in the WP database! But how do we add ’stuff’ to it? Again WordPress to the rescue as it has some rather useful built-in functions to allow us to do this, but the basics are: write an ‘update’ or ‘insert’ sql statement using the variables you want to include then use the $wpdb class to run the query.

Stage 6: Inserting data into our new table

{code type=php}
function record_wp_track_members() {
#Declare defaults for our variables
$today = date("Y-m-d");
$time = date("H:i:s");

#Create the sql statement to add todays time and date
$sql1 = "INSERT INTO `" . $wp_track_members_table ."` VALUES ('', '$today', ";
$sql1 .= "'$time')";

#Run the sql query
$result1 = $wpdb->query($sql1);
}#End of record_wp_track_members() function
?>
{/code}

Now we have that written, we need to tell WordPress when to run it...again, we'll include it in the footer.

{code type=php} add_action('wp_footer', 'record_wp_track_members');
?>
{/code}

So now, that code in full:

{code type=php}
function record_wp_track_members() {
#Declare defaults for our variables
$today = date("Y-m-d");
$time = date("H:i:s");

#Create the sql statement to add todays time and date
$sql1 = "INSERT INTO `" . $wp_track_members_table ."` VALUES ('', '$today', ";
$sql1 .= "'$time')";

#Run the sql query
$result1 = $wpdb->query($sql1);
}#End of record_wp_track_members() function

add_action('wp_footer', 'record_wp_track_members');
?>
{/code}

So there we have it! We've checked to see if a table exists, if not, created it and then we've inserted some data into it when the footer of the page loads.

There are many more WordPress Hooks you can use – so you can determine when all this happens (official codex docs).  There is official documentation for this here, but it’s always nice to have a real world example – plus this acts as a primer for the soon-to-be-released plugin which will be exclusive to WPToy!  Hope you enjoyed this, please feel free to leave questions or suggestions in the comments!