How To Create Database Table In WordPress

WordPress database is beautifully crafted so that in most cases we don’t need to create a custom database table. But on some occasions, if you need to do that then how you can create the custom database table in WordPress?

In this article, we will learn how to create a custom database table in WordPress without a plugin. Moreover, if you are creating a custom plugin then we will also see how to create the table upon plugin activation. So let’s just jump into it.

01 Create Custom Database Table In WordPress

As we know that WordPress uses the MySQL database services so that we have to use the CREATE TABLE statement to create the table.

The following illustrates the basic syntax of the CREATE TABLE statement:

CREATE TABLE [IF NOT EXISTS] table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
) ENGINE=storage_engine;

Let’s create a payment table with the default table prefix of your database table with 'id', 'user_id', 'created_at', and 'expired_at' columns.

function scratchcode_create_payment_table() {

	global $wpdb;

	$table_name = $wpdb->prefix . "payment";

	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
	  id bigint(20) NOT NULL AUTO_INCREMENT,
	  user_id bigint(20) UNSIGNED NOT NULL,
	  created_at datetime NOT NULL,
	  expires_at datetime NOT NULL,
	  PRIMARY KEY id (id)
	) $charset_collate;";

	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
	dbDelta($sql);
}	

add_action('init', 'scratchcode_create_payment_table');

The above function will run every-time when you refresh the page but it won’t create a table multiple times as we have added IF NOT EXISTS statement.

Where To Add This Snippet?

Most Important: Add the following code to your child theme’s functions.php file. If you add custom code directly to your parent theme’s functions.php file then it will be wiped entirely when you update the theme.

If you are using a custom theme and if it doesn’t require any update then you can directly place the code into wp-content/themes/your-theme/function.php file.

Please also note that we have tested all codes in the GeneratePress child theme & Storefront child theme.

02 Create Custom Database Table In Plugin On Activation

We can use the same code as we have done in the above example but we just need to use register_activation_hook in the plugin so that it runs only upon activation.

function scratchcode_create_payment_table() {

	global $wpdb;

	$table_name = $wpdb->prefix . "payment";

	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
	  id bigint(20) NOT NULL AUTO_INCREMENT,
	  user_id bigint(20) UNSIGNED NOT NULL,
	  created_at datetime NOT NULL,
	  expires_at datetime NOT NULL,
	  PRIMARY KEY id (id)
	) $charset_collate;";

	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
	dbDelta($sql);
}	

register_activation_hook( __FILE__, 'scratchcode_create_payment_table' );

Additionally, read our guide:

We hope this article helped you to create database table in WordPress.

Please let us know in the comments if everything worked as expected, your issues, or any questions. If you think this article saved your time & money, please do comment, share, like & subscribe. Thank you in advance 🙂. Keep Smiling! Happy Coding!