Create Database Tables for Your WordPress Plugin

In order to advance your WordPress plugin development (or even theme development), one of the steps you will need to make is learning how to create and work with additional database tables, which you can use to store information used by your plugin.

By creating extra tables in the WordPress database, you will be able to create an infinite number of different kinds of plugins. A plugin-specific table allows you to define the exact data structure you need for your development.

An example of a plugin using a custom database table, is one of my latest plugins: Sugar Slider – Slide Manager. In Sugar Slider’s case, two separate DB tables are used to store sliders and the individual slides for each slider.

Creating tables is really pretty simple. All you need to do is pass an SQL query to a core WordPress database function.

1
2
3
4
5
6
7
8

1
2
3
4
5
6
7
8

1
2
3
4
5
6
7
8

$sql = "CREATE TABLE my_table_name (
		id mediumint(9) NOT NULL AUTO_INCREMENT,
		one_column tinytext NOT NULL,
		another_column tinytext NOT NULL,
		UNIQUE KEY id (id)
		);";
		require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
		dbDelta($sql);

This is all you need to create a table called “my_table_name” that has two columns, “one_column”, and “one_column”. If you need more information about the SQL syntax, refer to the W3 Schools page about it.

The code above, however, requires some sort of “init” function to make the database creation actually fire, so what we do is put it into a function that runs when our plugin is activated.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

function my_plugin_create_table()
{
        // do NOT forget this global
	global $wpdb;
 
	// this if statement makes sure that the table doe not exist already
	if($wpdb->get_var("show tables like my_table_name") != 'my_table_name') 
	{
		$sql = "CREATE TABLE my_table_name (
		id mediumint(9) NOT NULL AUTO_INCREMENT,
		one_column tinytext NOT NULL,
		another_column tinytext NOT NULL,
		UNIQUE KEY id (id)
		);";
		require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
		dbDelta($sql);
	}
}
// this hook will cause our creation function to run when the plugin is activated
register_activation_hook( __FILE__, 'my_plugin_create_table' );

As long as this code is placed in your plugin’s main file, it will work great. When your plugin is activated, a new table will be created.

In upcoming posts, I will show you more about interacting with your plugin’s table and how to utilize these tools to create much better plugins.