How to create table with WordPress Plugin

Introduction

In this post we will discuss about how to create table with WordPress plugin. When you are writing a plugin for WordPress (a simple email subscription form), you will find that you need to store some information in the WordPress database. Certainly there are ways to store information and you can find more on this here but we will see here in simple way how to create a table and store information into database.

This article describes how your WordPress plugin will automatically create table in MySQL database server for storing its data.

This example does not show how to upgrade the table if you want to upgrade the plugin with different table structure. But you can read the developer’s documentation to find out information on how to write a function to upgrade the table structure.

Make sure the following things in order your plugin to create automatically table in MySQL database:

Write a function in PHP file, which will create a table
Ensure WordPress calls the function when plugin gets activated

So let’s see how we can create a MySQL table using WordPress plugin automatically.

Prerequisites

Make sure you read first on creating WordPress plugin tutorial.

Creating Table

We will create below PHP function into the plugin’s main file. This function will create database table in MySQL server.

The table store email address of the subscribed users who subscribe through subscription form.

function roytuts_on_activation(){
	// create the custom table
	global $wpdb;
	
	$table_name = $wpdb->prefix . 'roytuts_email_subscribers';
	$charset_collate = $wpdb->get_charset_collate();
	
	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
        id bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        email_id varchar(50) NOT NULL default '') $charset_collate;";
	
	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
	dbDelta( $sql );
}

In the above table we have retrieved the table prefix to maintain the uniformity with the existing table names.

In the wp-config.php file, the table name prefix is defined. The default table name prefix is wp_, if you want you can change it in the wp-config.php file.

We have also used $wpdb->get_charset_collate() to get the character set and collation.

Remember the following points while creating SQL statements for table creation as mentioned in the developer’s guide:

You must put each field on its own line in your SQL statement.
You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
KEY must be followed by a SINGLE SPACE then the key name then a space then open parenthesis with the field name then a closed parenthesis.
You must not use any apostrophes or backticks around field names.
Field types must be all lowercase.
SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.
You must specify the length of all fields that accept a length parameter. int(11), for example. If you use MySQL version 8 then you need not to specify the field length for int type.

We’ll use the dbDelta() function in wp-admin/includes/upgrade.php. The dbDelta() function examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary, so it can be very handy for updates.

Registering Activation Hook

As we need to execute the above function during plugin activation so we need to register the activation hook.

Notice we are calling the above function to create table during plugin activation.

register_activation_hook( __FILE__, 'roytuts_on_activation' );

Okay. So we have finished table creation script using PHP language for our plugin.

Still clicking on the subscription form users won’t be able to subscribe.

What’s next? We will allow user to subscribe and store user’s email address into the above table.

Source Code

Download

Thanks for reading.

Roy Tutorials