Create Database Tables for Your WordPress Plugin

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.

Comments (9)

  • Comment by Luis
    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
    Dhruv Thaker
    says:

    Nice tutorial. I was looking for wpdb.

  • Comment by alxvallejo
    alxvallejo
    says:

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

  • Comment by hdwallpaper4u
    hdwallpaper4u
    says:

    Really awesome tuts…it helps me lot

  • Comment by Pippin
    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
    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
    Pippin
    says:

    Show me the code you are trying to use.

  • Comment by Aryandev
    Aryandev
    says:

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

  • Comment by Sri Lanka Business Directory
    Sri Lanka Business Directory
    says:

    Great info …exactly what we looking for :)