How to Create Custom Database Tables for WordPress Plugins


Galleries, short codes, custom post types, image editing, and custom taxonomies generally don’t need their respective database tables as plugins are able to do so using the existing tables. Since the WordPress database schema, post tables are used in storing objects, and post meta tables are used in storing any additional information related to the objects. However, in some cases, it is wasteful, which is why many WordPress sites create new database tables.

wordpress plugins

The decision to create custom tables for plugins depends on the structure and amount of data. It benefits the user as the schemas are better suited to the data structure, giving more control over field types and limits. It also makes it easier to export data and scale applications that would feature complex queries.

However, custom tables may clutter the database, making data access difficult and increasing the likelihood of SQL errors and potential attacks. Now that you know what’s good and what’s bad with creating a custom database table, here is a step by step guide to get started!

Creating Database Table
You can create database tables through plugins and a function upon activation using the following:

register_activation_hook( __FILE__, 'my_plugin_create_db' );
function my_plugin_create_db() {
	// Create DB Here
}

Once the plugin is activated this function will get live, after which we will use it to add database tables through the dbDelta() function. To do so, you need to create the name, collation, and SQL query for the database, which is explained below:

function my_plugin_create_db() {


	global $wpdb;
	$charset_collate = $wpdb->get_charset_collate();
	$table_name = $wpdb->prefix . 'my_analysis';


	$sql = "CREATE TABLE $table_name (
		id mediumint(9) NOT NULL AUTO_INCREMENT,
		time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
		views smallint(5) NOT NULL,
		clicks smallint(5) NOT NULL,
		UNIQUE KEY id (id)
	) $charset_collate;";


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

We’ll collate from the WordPress config file for now, which is in stored in the $wpdb variable, and retrieve the prefix from it. The prefix is used in creating the database name while SQL is formatted in creating the database table. There are standard guidelines maintained by the dbDelta() function when it comes to formatting SQL.

Update Database Tables
Creating a database table doesn’t end your work since you would want to add new features to the plugin(s). In the example above, we added clicks and views features to the database, and now we’ll add an average page view time feature. For the sake of explaining, we’ll add the average time in minutes. To add this function, we will create a new column using the dbDelta() function again. But before that, let’s find out if we have added the version number to the plugin:

function my_plugin_create_db() {
  global $wpdb;
  $version = get_option( 'my_plugin_version', '1.0' );
  // ...
}

Assuming that nothing’s changed in 1.x versions, we’ll add a column to version 2.0 using the following:

function my_plugin_create_db() {


	global $wpdb;
  	$version = get_option( 'my_plugin_version', '1.0' );
	$charset_collate = $wpdb->get_charset_collate();
	$table_name = $wpdb->prefix . 'my_analysis';


	$sql = "CREATE TABLE $table_name (
		id mediumint(9) NOT NULL AUTO_INCREMENT,
		time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
		views smallint(5) NOT NULL,
		clicks smallint(5) NOT NULL,
		UNIQUE KEY id (id)
	) $charset_collate;";


	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
	dbDelta( $sql );
	
	if ( version_compare( $version, '2.0' ) < 0 ) {
		$sql = "CREATE TABLE $table_name (
		  id mediumint(9) NOT NULL AUTO_INCREMENT,
		  time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
		  views smallint(5) NOT NULL,
		  clicks smallint(5) NOT NULL,
		  blog_id smallint(5) NOT NULL,
		  UNIQUE KEY id (id)
		) $charset_collate;";
		dbDelta( $sql );
	
	  	update_option( 'my_plugin_version', '2.0' );
		
	}
	
}

As you can see, we have added the blog_id that now allows the plugin to work on multiple sites. Everything else is taken care of by the dbDelta() function since you have supplied the proper structure for the database schema. That’s it for now and don’t forget to share this post to anyone looking for help creating custom database tables for WordPress plugins!

You may also like to read