Puddinq.com sharing knowledge

Create custom tables in WordPress

Create custom tables in WordPress

It is advised to use the default database structure with custom posts and meta fields to store information, but sometimes when you have specific needs or want to scale the proportions of what is happening you want custom tables for your data. Here is a snippet that uses the global $wpdb end a default function maybe_create_table() to create one table:


    function installTables() {

        global $wpdb;

        // we need this to access the maybe_create_table function
        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

        $charset_collate = $wpdb->get_charset_collate();
        $table_tasks = $wpdb->prefix . "tasks";
        $columns_tasks = <<<COLUMNS
(
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `project_id` int(11) NOT NULL,
  `title` varchar(250) NOT NULL,
  `content` longtext NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `project_id` (`project_id`)
)
COLUMNS;

        // we are using this function so that if the table already exists, there are no complications
        maybe_create_table( $wpdb->prefix . $table_name, "CREATE TABLE {$table_tasks} {$columns_tasks} {$charset_collate};" );

    }

We are using maybe_create_table so that if this function is called in your register_activation_hook and the table already exists there will be no complications. This can be a good thing if removing the database is optional when uninstalling your theme or plugin.

A more direct way (without the maybe function)

function registerGenericTable($name)
{
    global $wpdb;
    // set the default character set and collation for the table
    $charset_collate = $wpdb->get_charset_collate();
    // Check that the table does not already exist before continuing
    $sql = "CREATE TABLE IF NOT EXISTS `{$wpdb->base_prefix}dog_{$name}` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `url` varchar(350) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `url_status` varchar(20) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `new_url` varchar(350) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `create_empty_page` varchar(20) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `post_type` varchar(100) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
  `post_id` bigint DEFAULT NULL,
  `blog_id` bigint DEFAULT NULL,
  `content` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
  `meta` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci,
  `scrape_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY  (ID),
   KEY (url)
  ) $charset_collate;
  ";
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
    return empty($wpdb->last_error); // true or false
}