Codeable info

Create Database Tables for Your WordPress Plugin

Posted on by in WordPress Tutorials

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
$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
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.

)
Codeable info

Comments (9)

Comment by Luis says:

Hello thank you very much for your post, could you help me with this code,
function baseDiarioAlimeto_install() {
global $wpdb;

$table_name = $wpdb->prefix . “diarioAlimento”;

$sql_meta = “CREATE TABLE ” . $table_name .” (
ID_registro bigint (20) NOT NULL AUTO_INCREMET,
id_usuario bigint (20) NOT NULL,
fecha datetime DEFAULT ‘0000-00-00 0000:00:00′ NOT NULL,
desayuno VARCHAR(100) DEFAULT ” NOT NULL,
snack1 VARCHAR(100) DEFAULT ” NOT NULL,
almuerzo VARCHAR(100) DEFAULT ” NOT NULL,
snack2 VARCHAR(100) DEFAULT ” NOT NULL,
cena VARCHAR(100) DEFAULT ” NOT NULL,
PRIMARY KEY (ID_registro)
);”;

require_once(ABSPATH . ‘wp-admin/includes/upgrade.php’);
dbDelta($sql_meta);
}
register_activation_hook( __FILE__, ‘baseDiarioAlimeto_install’);

Comment by Dhruv Thaker says:

Nice tutorial. I was looking for wpdb.

Comment by alxvallejo says:

You could also use:
CREATE TABLE IF NOT EXISTS
to verify if the table exists.

Comment by hdwallpaper4u says:

Really awesome tuts…it helps me lot

Comment by Pippin says:

@Michal – Yes, you’re completely correct. What I will usually do is store the DB name in a var, like this: $db_name = $wpdb->prefix . ‘my_db_name';

Comment by Michał Środek says:

@Pippin: remember to use database tables prefix. It’s available as a public variable in $wpdb.

$wpdb->prefix

Your plugin wont be working properly on two WordPress instances installed on the same database.

Comment by Pippin says:

Show me the code you are trying to use.

Comment by Aryandev says:

How to run function when pluings deactivated ????
i fond some code not working

Comment by Sri Lanka Business Directory says:

Great info …exactly what we looking for :)

Codeable info