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

Here is the code:

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?

Read more here: Issue Creating Indices on Created Tables for WordPress Plugin


Solution:

If you know the solution of this issue, please leave us a reply in Comment section, to update the question.

Related Wordpress search:

, , , , , ,

Wordpress related questions and answers: