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.

Comments

Your email address will not be published.