Resources
https://deliciousbrains.com/creating-custom-table-php-wordpress/
Create / Update A Table
While not a requirement to use the wordpress dbDelta function, it is recommended when making changes to the database as it examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary.
//ENSURE USER IS ADMIN
if (!current_user_can('administrator'))
die;
$output = "";
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
//----- CREATE / UPDATE my_tablename -----
$sql = "CREATE TABLE `{$wpdb->base_prefix}my_tablename` (
user_id bigint(20) UNSIGNED NOT NULL,
my_field1 varchar(255),
PRIMARY KEY (user_id)
) $charset_collate;";
dbDelta($sql);
$output = "Updating my_tablename: " . print_r($wpdb->last_result) . ", " . $wpdb->last_error;
Table Definitions
Various different column types and default values
$sql = "CREATE TABLE `{$wpdb->base_prefix}my_tablename` (
user_id bigint(20) UNSIGNED NOT NULL,
my_tinyint tinyint UNSIGNED DEFAULT 0,
my_smalling smallint UNSIGNED DEFAULT 0,
my_int int NOT NULL DEFAULT 10000,
CreatedDateTime datetime DEFAULT NULL,
my_string varchar(255) DEFAULT '',
my_text text DEFAULT '',
PRIMARY KEY (user_id)
) $charset_collate;";
Defining character set
LiveSearchMeta varchar(5000) CHARACTER SET utf8 DEFAULT '',
Defining an index
//Index 1 column
$sql = "CREATE TABLE `{$wpdb->base_prefix}my_tablename` (
user_id bigint(20) UNSIGNED NOT NULL,
my_field1 tinyint,
my_field2 tinyint,
my_field3 tinyint,
PRIMARY KEY (user_id),
INDEX MyIndexName (my_field1)
) $charset_collate;"; //MyIndexName can be the same as the field name if desired
//Index 2 columns separatly, 1 of them ensure uniqueness
$sql = "CREATE TABLE `{$wpdb->base_prefix}my_tablename` (
user_id bigint(20) UNSIGNED NOT NULL,
my_field1 tinyint,
my_field2 tinyint,
my_field3 tinyint,
PRIMARY KEY (user_id),
INDEX MyIndexName (my_field1),
UNIQUE INDEX MyIndexName (my_field2)
) $charset_collate;"; //MyIndexName can be the same as the field name if desired
//Index 3 columns as 1 index and ensure uniqueness
$sql = "CREATE TABLE `{$wpdb->base_prefix}my_tablename` (
user_id bigint(20) UNSIGNED NOT NULL,
my_field1 tinyint,
my_field2 tinyint,
my_field3 tinyint,
PRIMARY KEY (user_id),
UNIQUE INDEX MyIndexName (my_field1, my_field2, my_field3)
) $charset_collate;";
USEFUL?
We benefit hugely from resources on the web so we decided we should try and give back some of our knowledge and resources to the community by opening up many of our company’s internal notes and libraries through mini sites like this. We hope you find the site helpful.
Please feel free to comment if you can add help to this page or point out issues and solutions you have found, but please note that we do not provide support on this site. If you need help with a problem please use one of the many online forums.