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;";