Issue Creating Indices on Created Tables for WordPress Plugin

I’m having an issue getting some indices created when I create my tables for a new WordPress plugin.

Here is the code:

Read More
global $wpdb;
$em_posts = $wpdb->prefix . "em_collab_posts";
$em_groups = $wpdb->prefix . "em_collab_groups";
$em_users = $wpdb->prefix . "em_collab_users";
if($wpdb->get_var("SHOW TABLES LIKE '$em_users'") != $em_users) {
    $sql = "CREATE TABLE IF NOT EXISTS `$em_users` (
                `cuID` bigint(20) NOT NULL,
                `groupID` bigint(20) NOT NULL,
                `userID` bigint(20) NOT NULL,
                `cuType` int(11) NOT NULL,
                `cuSettings` longtext NOT NULL,
                `cuCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `cuUpdated` datetime DEFAULT NULL
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;";
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    // Create the table
    dbDelta($sql);
    // Add the indices
    dbDelta("ALTER TABLE `$em_users`
            ADD PRIMARY KEY (`cuID`), ADD KEY `groupID` (`groupID`), ADD KEY `userID` (`userID`), ADD KEY `cuType` (`cuType`); ");
    dbDelta("ALTER TABLE `$em_users`
            MODIFY `cuID` bigint(20) NOT NULL AUTO_INCREMENT;");
}
if($wpdb->get_var("SHOW TABLES LIKE '$em_posts'") != $em_posts) {
    $sql = "CREATE TABLE IF NOT EXISTS `$em_posts` (
                `cpID` bigint(20) NOT NULL,
                `groupID` bigint(20) NOT NULL,
                `post_id` bigint(20) NOT NULL,
                `cpSettings` longtext NOT NULL,
                `cpCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `cpUpdated` datetime DEFAULT NULL
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    // Create the table
    dbDelta($sql);
    // Add the indices
    dbDelta("ALTER TABLE `$em_posts`
                ADD PRIMARY KEY (`cpID`), ADD KEY `groupID` (`groupID`), ADD KEY `post_id` (`post_id`);");
    dbDelta("ALTER TABLE `$em_posts`
                MODIFY `cpID` bigint(20) NOT NULL AUTO_INCREMENT;");
}
if($wpdb->get_var("SHOW TABLES LIKE '$em_groups'") != $em_groups) {
    $sql = "CREATE TABLE IF NOT EXISTS `$em_groups` (
                `groupID` bigint(20) NOT NULL,
                `gTitle` varchar(50) NOT NULL,
                `gDescription` longtext NOT NULL,
                `gSettings` longtext NOT NULL,
                `gCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `gUpdated` datetime DEFAULT NULL
            ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    // Create the table
    dbDelta($sql);
    // Add the indices
    dbDelta("ALTER TABLE `$em_groups`
                ADD PRIMARY KEY (`groupID`), ADD KEY `gTitle` (`gTitle`);");
    dbDelta("ALTER TABLE `$em_groups`
                MODIFY `groupID` bigint(20) NOT NULL AUTO_INCREMENT;");
}

I also tried it with the ALTER TABLE statements in with the actual creation, the tables themselves get created, but the indices do not.

What am I doing wrong? Or is there a different way to do this?

Related posts

Leave a Reply

1 comment

  1. Needed to change the initial query’s

    Updated Code – Works

    $em_posts = $wpdb->prefix . "em_collab_posts";
    $em_groups = $wpdb->prefix . "em_collab_groups";
    $em_users = $wpdb->prefix . "em_collab_users";
    if($wpdb->get_var("SHOW TABLES LIKE '$em_users'") != $em_users) {
        $sql = "CREATE TABLE IF NOT EXISTS `$em_users` (
                    `cuID` bigint(20) NOT NULL auto_increment,
                    `groupID` bigint(20) NOT NULL,
                    `userID` bigint(20) NOT NULL,
                    `cuType` int(11) NOT NULL,
                    `cuSettings` longtext NOT NULL,
                    `cuCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    `cuUpdated` datetime DEFAULT NULL,
                    PRIMARY KEY (`cuID`),
                    KEY `groupID` (`groupID`),
                    KEY `userID` (`userID`),
                    KEY `cuType` (`cuType`)
                ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;";
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        // Create the table
        dbDelta($sql);
    }
    if($wpdb->get_var("SHOW TABLES LIKE '$em_posts'") != $em_posts) {
        $sql = "CREATE TABLE IF NOT EXISTS `$em_posts` (
                    `cpID` bigint(20) NOT NULL auto_increment,
                    `groupID` bigint(20) NOT NULL,
                    `post_id` bigint(20) NOT NULL,
                    `cpSettings` longtext NOT NULL,
                    `cpCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    `cpUpdated` datetime DEFAULT NULL,
                    PRIMARY KEY (`cpID`),
                    KEY `groupID` (`groupID`),
                    KEY `post_id` (`post_id`)
                ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        // Create the table
        dbDelta($sql);
    }
    if($wpdb->get_var("SHOW TABLES LIKE '$em_groups'") != $em_groups) {
        $sql = "CREATE TABLE IF NOT EXISTS `$em_groups` (
                `groupID` bigint(20) NOT NULL auto_increment,
                    `gTitle` varchar(50) NOT NULL,
                    `gDescription` longtext NOT NULL,
                    `gSettings` longtext NOT NULL,
                    `gCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    `gUpdated` datetime DEFAULT NULL,
                    PRIMARY KEY (`groupID`)
                ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;";
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        // Create the table
        dbDelta($sql);
    }