Create custom database table in WordPress

This short code snippet is about creating a custom database table in WordPress. It can be done through a custom plugin or simply through the child theme.

In this example I will be creating a table with following fields.

id – Unique ID for each DB record.

item_id – ID of any post/page/custom post.

voted_by – User ID of a user who has voted for any item.

ip_address – IP address of the voter.

voted_on – Date and time of the vote.

Creating a database table when a plugin is activated

Just paste this code snippet into the custom plugin’s main file and it will create the custom table when the plugin will be activated.

register_activation_hook( __FILE__, 'create_new_wpdb_table' );
function create_new_wpdb_table() {
	global $wpdb;
	$table_name = $wpdb->prefix . 'votes';
	$charset_collate = $wpdb->get_charset_collate();
	$query = "CREATE TABLE $table_name (
		id int(6) NOT NULL AUTO_INCREMENT,
		item_id int(9) NOT NULL,
		voted_by bigint(20) UNSIGNED NOT NULL,
		ip_address varchar(15),
		voted_on datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
		PRIMARY KEY  (id)
	) $charset_collate;";
	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
	dbDelta( $query );
}

Creating a custom table without any custom plugin

Custom database table can also be created without a plugin. Just place the following code into functions.php file of the active theme. Now to create the table just open any page of the website once and the table will be created .

Note –  Once the custom database table is created remove this code or comment it otherwise it will get executed on every page refresh and put unnecessary load on the server

add_action('init','create_new_wpdb_table');
function create_new_wpdb_table() {
	global $wpdb;
	$table_name = $wpdb->prefix . 'votes';
	$charset_collate = $wpdb->get_charset_collate();
	$query = "CREATE TABLE $table_name (
		id int(6) NOT NULL AUTO_INCREMENT,
		item_id int(9) NOT NULL,
		voted_by bigint(20) UNSIGNED NOT NULL,
		ip_address varchar(15),
		voted_on datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
		PRIMARY KEY  (id)
	) $charset_collate;";
	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
	dbDelta( $query );
}